Get Top Items by Group

Question

Get Top Items by Group

peter.a.day

Grasshopper

Points: 21
More actions
November 7, 2008 at 5:43 am

#195985

I have a problem which I have simplified into the following example. Say I have a recording of events happening. My table might be:
[font="Courier New"]Id User Item EventTime
1 Bob AAA 2008-02-02 11:02:51.000
2 Bob BBB 2008-02-04 09:10:52.000
3 Sue CCC 2008-02-04 15:00:13.000
4 Bob DDD 2008-02-04 16:02:05.000
5 Peter EEE 2008-02-07 16:03:43.000
6 Sue FFF 2008-02-07 18:12:01.000
7 Sue GGG 2008-02-12 08:08:03.000
8 Bob HHH 2008-02-13 14:02:43.000
9 Fred III 2008-02-22 16:12:44.000
10 Bob JJJ 2008-02-24 12:05:10.000
11 Peter KKK 2008-02-24 14:15:58.000
12 Bob LLL 2008-02-25 18:02:35.000
[/font]
What I want to get as a result is the last item for each user, that is the result set would be:
Bob, LLL
Peter, KKK
Fred, III
Sue, GGG
I am really struggling to find a way of getting this result set; it looks at first glance to be easy. I have tried all the simple GROUP commands and even gone into RANK() functions but can't produce the answer I want.
Can anyone help please?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

peter.a.day (11/7/2008)
I have a problem which I have simplified into the following example. Say I have a recording of events happening. My table might be:
[font="Courier New"]Id User Item EventTime
1 Bob AAA 2008-02-02 11:02:51.000
2 Bob BBB 2008-02-04 09:10:52.000
3 Sue CCC 2008-02-04 15:00:13.000
4 Bob DDD 2008-02-04 16:02:05.000
5 Peter EEE 2008-02-07 16:03:43.000
6 Sue FFF 2008-02-07 18:12:01.000
7 Sue GGG 2008-02-12 08:08:03.000
8 Bob HHH 2008-02-13 14:02:43.000
9 Fred III 2008-02-22 16:12:44.000
10 Bob JJJ 2008-02-24 12:05:10.000
11 Peter KKK 2008-02-24 14:15:58.000
12 Bob LLL 2008-02-25 18:02:35.000
[/font]
What I want to get as a result is the last item for each user, that is the result set would be:
Bob, LLL
Peter, KKK
Fred, III
Sue, GGG
I am really struggling to find a way of getting this result set; it looks at first glance to be easy. I have tried all the simple GROUP commands and even gone into RANK() functions but can't produce the answer I want.
Can anyone help please?

i dont have the data but may be this query work: it is not tested

select user, item, count(*) from events group by user order by user desc

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

peter.a.day Grasshopper Points: 21 More actions · Answer 2

KrayKnot

Thanks for the attempt, but I'm afraid your idea doesn't work because Item is in the select list but "not in the aggregate function or GROUP BY clause". Taking Item out of the select list returns the user name and a count of the number of entries in the table

I still think this problem is actually more difficult than it first seems

PAD

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Try this:

[font="Courier New"]

DECLARE @MyTable TABLE

(

[Id] INT,

[User] VARCHAR(10),

[Item] CHAR(3),

[EventTime] DATETIME

)

INSERT INTO @MyTable

SELECT 1,'Bob','AAA','2008-02-02 11:02:51.000' UNION ALL

SELECT 2,'Bob','BBB','2008-02-04 09:10:52.000' UNION ALL

SELECT 3,'Sue','CCC','2008-02-04 15:00:13.000' UNION ALL

SELECT 4,'Bob','DDD','2008-02-04 16:02:05.000' UNION ALL

SELECT 5,'Peter','EEE','2008-02-07 16:03:43.000' UNION ALL

SELECT 6,'Sue','FFF','2008-02-07 18:12:01.000' UNION ALL

SELECT 7,'Sue','GGG','2008-02-12 08:08:03.000' UNION ALL

SELECT 8,'Bob','HHH','2008-02-13 14:02:43.000' UNION ALL

SELECT 9,'Fred','III','2008-02-22 16:12:44.000' UNION ALL

SELECT 10,'Bob','JJJ','2008-02-24 12:05:10.000' UNION ALL

SELECT 11,'Peter','KKK','2008-02-24 14:15:58.000' UNION ALL

SELECT 12,'Bob','LLL','2008-02-25 18:02:35.000'

/*

Bob, LLL

Peter, KKK

Fred, III

Sue, GGG

*/

SELECT * FROM @MyTable ORDER BY [User],[EventTime]

;WITH MyCTE ([RowNum],[User],[Item])

AS

(SELECT

ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [EventTime] DESC) AS RowNum

,[User]

,[Item]

FROM @MyTable)

SELECT [User],[Item]

FROM MyCTE

WHERE RowNum = 1[/font]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

peter.a.day Grasshopper Points: 21 More actions · Answer 4

Christopher

Thank you - that is the answer 😀

I need to go and read up on the PARTITION function (I understand RANK and ROW_NUMBER). I guessed that it might need to be a Common Table Expression

PAD

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

yes read up about the partitioning it's sort of like a "GRouping"

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Dugi SSCoach Points: 17998 More actions · Answer 6

yes the row_number() combination with CTE works perfect to find the values as you want to show couz you have also other function like rank() dense_rank() Ntile()

great post here, Christopher Stobbs

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]