Duplicates in SELECT (DISTINCT/GROUP BY)...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicates in SELECT (DISTINCT/GROUP BY)...

Author

Topic

NewMedia42
Starting Member

35 Posts

Posted - 2001-10-20 : 15:00:28

I have a pretty straightforward database setup in the following way:

ID (int) Name (vchar) Rating (int)
---------- -------------- -------------
1 CompanyA 12
2 CompanyB 55
3 CompanyA 32
4 CompanyC 99
5 CompanyD 5
6 CompanyA 22

Now in the past I've returned the top x rows using the following SELECT string:

SELECT TOP 4 [Table].ID,[Table].Name,[Table].Rating FROM [Table] ORDER BY [Table].Rating DESC

Which returns:
4,CompanyC,99
2,CompanyB,55
3,CompanyA,32
6,CompanyA,22

When I need it to return:

4,CompanyC,99
2,CompanyB,55
3,CompanyA,32
5,CompanyD,5

I've tried adding distinct to the query:

SELECT DISTINCT TOP 4 [Table].ID,[Table].Name,[Table].Rating FROM [Table] ORDER BY [Table].Rating DESC

But it just returns the same thing - I'm assuming it's considering ID, Name, and Rating in the DISTINCT evaluation.

I've also attempted to resolve this with the GROUP BY instruction, but then I'm unable to get the ID that corresponds with the largest rating (even though they share the same name, they are different making any sort of min/max evaluation useless).

Any help would be greatly appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources