Select query confusion - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Select query confusion

Author

Topic

gorebash
Starting Member

1 Post

Posted - 2008-04-14 : 18:44:47

The problem in short:
I have a select where I need to group by one column and sort by another column on the same table, without including the sort column in the result set.

The gorey details:
Allow me to illustrate. Table UserContent has three columns: Id (PK), UserId (FK), Rating (int), and DateCreated (datetime). From this table I need to select the top 3 UserId’s from records with the highest Rating values. The result set needs to also be distinct by UserId. So if the same user has the top 3 Ratings, he should only be shown once in the result set.

Additionally, if two or more of the top rating values are equal, it needs to select the one that was created first, hence the sorting on DateCreated.

I’m using SQL Server 2005. I’ve tried selecting the max(rating) and group by UserId, but then I can’t find any way to apply the sort.

Thanks!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 18:48:30

Hi gorebash

You should give some data and the result you would want for that data...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 18:54:26

[code]DECLARE @Sample TABLE (ID INT, UserID INT, Rating INT, DateCreated DATETIME)

INSERT @Sample
SELECT 1, 1, 9, '20080410' UNION ALL
SELECT 2, 1, 7, '20080408' UNION ALL
SELECT 3, 2, 8, '20080409' UNION ALL
SELECT 4, 3, 8, '20080409' UNION ALL
SELECT 5, 4, 6, '20080407'

SELECT TOP 3 ID,
UserID,
Rating,
DateCreated
FROM (
SELECT ID,
UserID,
Rating,
DateCreated,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Rating DESC) AS RecID
FROM @Sample
) AS d
WHERE RecID = 1
ORDER BY Rating DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources