Get TOP n records of each user

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

Transact-SQL (2000)

Get TOP n records of each user

Author

Topic

sunnysak
Starting Member

7 Posts

Posted - 2005-05-02 : 18:15:44

Hello Guys,

One more help needed please?

In the following example I want to get a list of UserID pulled from the top 3 records for each in the list that has value <80

Select ID from abc where (Score <80 (off latest Top 3 records of that ID))

table-> abc
ID Value Date
--------------------------
A1111 0 3/30/2005 13:05
A1111 80 3/9/2005 11:57
A1111 40 3/4/2005 9:31
A1111 80 2/23/2005 7:04
A1111 86 2/17/2005 9:57
A1111 86 1/28/2005 9:44
A1111 80 1/20/2005 6:37
A1111 83 1/6/2005 12:58
A1111 80 12/3/2004 11:21
ARQUE 83 12/14/2004 14:27
ARQUE 67 12/14/2004 14:26
ARQUE 83 12/14/2004 14:25
ARQUE 83 12/14/2004 14:25
AAHAM 80 2/23/2005 6:17
AAHAM 33 2/4/2005 5:52
AAHAM 57 1/24/2005 14:53
AAHAM 80 12/2/2004 21:22
AVEDO 80 4/22/2005 23:12
AVEDO 64 4/7/2005 21:05
AVEDO 60 3/27/2005 22:23
AVEDO 80 2/7/2005 16:47
AVEDO 80 1/8/2005 16:15
AVEDO 80 1/8/2005 16:15
ADCUL 83 3/17/2005 19:15
ADCUL 80 3/3/2005 22:15
AAGAR 75 3/29/2005 12:47
AAGAR 80 3/4/2005 6:00
AAGAR 50 2/21/2005 20:48
AAGAR 75 4/22/2005 14:21

---------OUTPUT-------------------
so basically the output from above list should be
A1111 0 3/30/2005 13:05
ARQUE 67 12/14/2004 14:26
AAHAM 33 2/4/2005 5:52
AVEDO 60 3/27/2005 22:23
AAGAR 50 2/21/2005 20:48

ADCUL won't appear since he doesn't have any value <80
----------------------------------------------

Hopefully I explained it well. Else, let me know I'll try again

Thanks for your help

Sunnysak

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-03 : 00:34:45

use a group by on the id, select the top 3 using top, subquerying the grouping

HTH

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-03 : 01:26:52

Check this whether this helps you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47494

Madhivanan

Failing to plan is Planning to fail

sunnysak
Starting Member

7 Posts

Posted - 2005-05-03 : 01:32:20

Ya finally I figured it out. works just the way I want it. Took a loong while, maybe I's not thinking right. Thanks for your help guys and here is my Statement for somebody who is looking.. :)

SELECT * FROM <table_name_1> T WHERE (id IN
(SELECT TOP 3 id
FROM <table_name_1> T1
ORDER BY <any_field1>, <date_field> DESC))
ORDER BY <anyfield1>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources