• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Weeding out duplicate values (using DISTINCT)

Environment: MS SQL Server 2000

Say I have a table like the following:

identity,userid,colour,flavour
1,1,blue,vanilla
2,1,blue,strawberry
3,2,red,chocolate
4,2,red,chocolate
5,3,black,licorice

'SELECT DISTINCT userid, colour, flavour' blocks the display of row 4 (since every element in that row was already returned with row 3), but not row 2 (since the flavour value differs).  How do I return only one row per userid value?

I haven't found any working examples of DISTINCT ORDER BY (which sounds like it would be useful).

Thanks in advance,

C
0
caylt
Asked:
caylt
  • 2
  • 2
1 Solution
 
rafranciscoCommented:
SELECT *
FROM YourTable A
WHERE A.Identity = (SELECT MAX(Identity) FROM YourTable B
                              WHERE A.UserID = B.UserID)
0
 
Brian CroweDatabase AdministratorCommented:
It sounds like is that you want a single entry per userid/color/flavor combo

SELECT Min(identity), userid, colour, flavour
FROM myTable
GROUP BY userid, colour, flavour
0
 
Brian CroweDatabase AdministratorCommented:
oops like i misread...

SELECT *
FROM myTable
WHERE myTable.identity IN (SELECT TOP 1 identity FROM myTable B
     WHERE B.userid = myTable.userid
     ORDER BY {insert your order here to determine which record is listed first})
0
 
stevetheskiCommented:
for performance reasons i would use the following
SELECT identity, userid, colour, flavour
FROM  
(SELECT Min(identity) as identity, userid, colour, flavour
FROM myTable
GROUP BY userid, colour, flavour) as innerTable
order By 1
0
 
stevetheskiCommented:
The GROUP BY clause can be sped up if you follow these suggestion:

Keep the number of rows returned by the query as small as possible.

 

Keep the number of groupings as few as possible.

 
reason for my previous post
 snippet from  http://www.sql-server-performance.com/transact_sql.asp


Don't group redundant columns.


If there is a JOIN in the same SELECT statement that has a GROUP BY, try to rewrite the query to use a subquery instead of using a JOIN. If this is possible, performance will be faster. If you have to use a JOIN, try to make the GROUP BY column from the same table as the column or columns on which the set function is used.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now