DISTINCT keyword not working.

DISTINCT keyword not working.

Post by Josh Ker » Fri, 28 Dec 2001 05:18:23



I need help getting the DISTINCT keyword to work.

I'm trying to return records with a unique subID value.  I set the DISTINCT
keyword before the subID value but it still returns multiple subID's.  Any
help would be great.  Thanks.

Here is my stored procedure:


As



 select
 DISTINCT S.subID,
 S.forumID,
 S.topicID,
 S.memberID,
 S.Active,
 S.goInbox,
 S.goEmail,
 T.Topic_ID,
 M.M_Name,
 F.F_Name,
 SUBSTRING(T.T_Subject,0,50) as T_Subject,
 T.T_Originator,
 T.T_Date,
 T.T_Last_Post
 from subscriptions S
 join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
T.Forum_ID = S.forumID)
 left outer join Members M on Member_ID = T.T_Originator
 left outer join Forum F on F.Forum_ID = T.forum_ID

 ORDER BY T.T_Last_Post DESC,T.T_Date DESC
 return

 
 
 

DISTINCT keyword not working.

Post by Steve Kas » Fri, 28 Dec 2001 05:28:48


Josh,

  DISTINCT applies to the entire select list.  You need to define what you
want more clearly.  If you are getting repeated subIDs from this query, then
you must have more than one value of some of the other columns for a given
subID.  In order to say how you might write this, you need to explain just
which
values of forumID, topicID, memberID, Active, ... you want for a given subID,
since there is more than one.

Steve Kass
Drew University


> I need help getting the DISTINCT keyword to work.

> I'm trying to return records with a unique subID value.  I set the DISTINCT
> keyword before the subID value but it still returns multiple subID's.  Any
> help would be great.  Thanks.

> Here is my stored procedure:

> Alter Procedure getSubscriptionDefs

> As



>  select
>  DISTINCT S.subID,
>  S.forumID,
>  S.topicID,
>  S.memberID,
>  S.Active,
>  S.goInbox,
>  S.goEmail,
>  T.Topic_ID,
>  M.M_Name,
>  F.F_Name,
>  SUBSTRING(T.T_Subject,0,50) as T_Subject,
>  T.T_Originator,
>  T.T_Date,
>  T.T_Last_Post
>  from subscriptions S
>  join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
> T.Forum_ID = S.forumID)
>  left outer join Members M on Member_ID = T.T_Originator
>  left outer join Forum F on F.Forum_ID = T.forum_ID

>  ORDER BY T.T_Last_Post DESC,T.T_Date DESC
>  return


 
 
 

DISTINCT keyword not working.

Post by Tom Morea » Fri, 28 Dec 2001 05:28:49


To diagnose this properly, we would need the DDL and sample data for all
tables.  While the subID may be unique in the subscriptions table, it is
possible, that there are:

multiple topics per subscription
multiple members per topic
multiple forums per topic

The DISTINCT keyword applies to the entire row.  Check your data and see
where there are multiples.  Check your output and see that the row itself is
unique.

--
   Tom

----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional

Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
I need help getting the DISTINCT keyword to work.

I'm trying to return records with a unique subID value.  I set the DISTINCT
keyword before the subID value but it still returns multiple subID's.  Any
help would be great.  Thanks.

Here is my stored procedure:


As



select
DISTINCT S.subID,
S.forumID,
S.topicID,
S.memberID,
S.Active,
S.goInbox,
S.goEmail,
T.Topic_ID,
M.M_Name,
F.F_Name,
SUBSTRING(T.T_Subject,0,50) as T_Subject,
T.T_Originator,
T.T_Date,
T.T_Last_Post
from subscriptions S
join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
T.Forum_ID = S.forumID)
left outer join Members M on Member_ID = T.T_Originator
left outer join Forum F on F.Forum_ID = T.forum_ID

ORDER BY T.T_Last_Post DESC,T.T_Date DESC
return

 
 
 

DISTINCT keyword not working.

Post by Josh Ker » Fri, 28 Dec 2001 09:26:41


There are multiple topics per subscription.  You basically subscribe to a
particular forum and you will get all the topics to that forum.


> To diagnose this properly, we would need the DDL and sample data for all
> tables.  While the subID may be unique in the subscriptions table, it is
> possible, that there are:

> multiple topics per subscription
> multiple members per topic
> multiple forums per topic

> The DISTINCT keyword applies to the entire row.  Check your data and see
> where there are multiples.  Check your output and see that the row itself
is
> unique.

> --
>    Tom

> ----------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
> SQL Server MVP
> Columnist, SQL Server Professional

> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> www.apress.com
> ---

> I need help getting the DISTINCT keyword to work.

> I'm trying to return records with a unique subID value.  I set the
DISTINCT
> keyword before the subID value but it still returns multiple subID's.  Any
> help would be great.  Thanks.

> Here is my stored procedure:

> Alter Procedure getSubscriptionDefs

> As



> select
> DISTINCT S.subID,
> S.forumID,
> S.topicID,
> S.memberID,
> S.Active,
> S.goInbox,
> S.goEmail,
> T.Topic_ID,
> M.M_Name,
> F.F_Name,
> SUBSTRING(T.T_Subject,0,50) as T_Subject,
> T.T_Originator,
> T.T_Date,
> T.T_Last_Post
> from subscriptions S
> join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
> T.Forum_ID = S.forumID)
> left outer join Members M on Member_ID = T.T_Originator
> left outer join Forum F on F.Forum_ID = T.forum_ID

> ORDER BY T.T_Last_Post DESC,T.T_Date DESC
> return

 
 
 

DISTINCT keyword not working.

Post by Tom Morea » Fri, 28 Dec 2001 20:54:22


There you have it.  If you put just the subID, Topic_ID and forumID in the
SELECT list, you will see that a SELECT DISTINCT will give you all distinct
values.  If you want the subID's to be unique in the result set, you will
have to remove the columns from the other tables in the SELECT list.

--
   Tom

----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional

Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
There are multiple topics per subscription.  You basically subscribe to a
particular forum and you will get all the topics to that forum.


> To diagnose this properly, we would need the DDL and sample data for all
> tables.  While the subID may be unique in the subscriptions table, it is
> possible, that there are:

> multiple topics per subscription
> multiple members per topic
> multiple forums per topic

> The DISTINCT keyword applies to the entire row.  Check your data and see
> where there are multiples.  Check your output and see that the row itself
is
> unique.

> --
>    Tom

> ----------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
> SQL Server MVP
> Columnist, SQL Server Professional

> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> www.apress.com
> ---

> I need help getting the DISTINCT keyword to work.

> I'm trying to return records with a unique subID value.  I set the
DISTINCT
> keyword before the subID value but it still returns multiple subID's.  Any
> help would be great.  Thanks.

> Here is my stored procedure:

> Alter Procedure getSubscriptionDefs

> As



> select
> DISTINCT S.subID,
> S.forumID,
> S.topicID,
> S.memberID,
> S.Active,
> S.goInbox,
> S.goEmail,
> T.Topic_ID,
> M.M_Name,
> F.F_Name,
> SUBSTRING(T.T_Subject,0,50) as T_Subject,
> T.T_Originator,
> T.T_Date,
> T.T_Last_Post
> from subscriptions S
> join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
> T.Forum_ID = S.forumID)
> left outer join Members M on Member_ID = T.T_Originator
> left outer join Forum F on F.Forum_ID = T.forum_ID

> ORDER BY T.T_Last_Post DESC,T.T_Date DESC
> return

 
 
 

1. top and distinct keyword did not work together

Hi,

I have to fetch the first record from a search query as follows.

"Select distinct(stuId), stuName from student where age > 10"

Some times the query returns a large volume of data and load the memory. So
I can't use front end.

If I put top in front of distinct then the query returns error.

How to get top record in a query which uses distinct keyword.

TIA

regards
Raj

2. Washington - Oracle Developer

3. keyword "NAME" not reserved keyword

4. Problem creating Roles through DSO code

5. Why is this distinct not working

6. UPDATE with XLOCK hint not atomic ?

7. urgent : query distinct not work

8. Fast/Cheap way to learn Sybase?

9. DISTINCT not working correct

10. Distinct command not working as intended

11. select statement with distinct not working

12. Why does SELECT COUNT(DISTINCT price) .. not work

13. Oracle Select DISTINCT used to work, now does not