how to write query for getting Top3 rows from each userId

Question

how to write query for getting Top3 rows from each userId

rudramurthyk

Grasshopper

Points: 15
More actions
April 10, 2010 at 11:32 am

#233665

HI,
i have one table from that table i want top3 rows from each userid , here userid is not unique, each userid can have many properties. so i want top3 rows in each userid, can any one tell how to get it. here rowid is unique and primarykey also.
please help me it,s urgent.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

If you would provide the table definition ... some sample data, required results formatted as described in the first link in my signature block you will vastly increase your chances of receiving a tested answer.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You can use the ROW_NUMBER() function. It would have been helpful if you would have given us the table structure and some sample data to work with.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Something like this will help you

WITH CTE(ROW_NUM , ID) AS

(

SELECT ROW_NUMBER() OVER(ORDER BY USERID) ROW_NUM, USERID FROM YOUR_TABLE

)

SELECT ROW_NUM, ID FROM CTE WHERE ROW_NUM IN (1,2,3) ORDER BY ID

Now lets have a ride, shall we!!! how about you going through the following article on "How to post your question to get the best help"??

FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Having said what I said in my post above and having some free time ... will this be what you want?

Note you have not defined what you consider the top 3

rows

--===Table definition and sample data you should supply when

--asking for assistance

CREATE TABLE #T([Rowid] [int] IDENTITY(1,1) NOT NULL,

[Userid] [int] NOT NULL,[Property] [varchar](10) NOT NULL)

INSERT INTO #T(Userid,Property)

SELECT 1,'A' UNION ALL

SELECT 2,'C' UNION ALL

SELECT 1,'C' UNION ALL

SELECT 4,'xx' UNION ALL

SELECT 1,'B' UNION ALL

SELECT 2,'Y' UNION ALL

SELECT 1,'4th'

--==Possilbe solution

;with numbered as(SELECT rowno=row_number() over

(partition by UserId order by Userid),Rowid, UserId, Property FROM #T)

select * from numbered WHERE rowno <= 3

/* Results:

rownoRowidUserIdProperty

111A

231C

351B

162Y

222C

144xx

or change the final select to:

select userid,property from numbered WHERE rowno <= 3

--== for these results

useridproperty

1A

1C

1B

2Y

2C

4xx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 5

One more variation on the theme:

DECLARE @Sample

TABLE (

row_id INTEGER NOT NULL

IDENTITY (1, 1)

PRIMARY KEY,

[user_id] SYSNAME NOT NULL,

login_time DATETIME NOT NULL,

UNIQUE ([user_id], login_time DESC)

);

INSERT @Sample

([user_id], login_time)

SELECT N'User 1', N'2010-03-01T14:32:57.064' UNION ALL

SELECT N'User 1', N'2010-03-02T17:29:13.456' UNION ALL

SELECT N'User 1', N'2010-03-02T09:31:34.872' UNION ALL

SELECT N'User 1', N'2010-03-03T08:34:11.494' UNION ALL

SELECT N'User 2', N'2010-03-01T11:23:17.099' UNION ALL

SELECT N'User 2', N'2010-03-01T14:43:52.145' UNION ALL

SELECT N'User 2', N'2010-03-02T09:33:33.812' UNION ALL

SELECT N'User 2', N'2010-03-03T12:18:19.732' UNION ALL

SELECT N'User 2', N'2010-03-02T15:07:22.627';

SELECT S.[user_id],

iTVF.login_time

FROM (

SELECT DISTINCT

user_id

FROM @Sample

) S

CROSS

APPLY (

SELECT TOP (3)

S2.login_time

FROM @Sample S2

WHERE S2.[user_id] = S.[user_id]

ORDER BY

S2.login_time DESC

) iTVF

ORDER BY

S.user_id,

iTVF.login_time DESC;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi