how to write query for getting Top3 rows from e

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)

how to write query for getting Top3 rows from e

Author

Topic

rudramurthyk
Starting Member

1 Post

Posted - 2010-04-10 : 13:43:50

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.

I am using sqlserver 2000

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-10 : 14:23:35

How about this:

SELECT RowID, UserID, Data -- Replace data with your columns
FROM (
		SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY RowID) AS r, 
				RowID, UserID, Data  -- Replace data with your columns
		FROM tableName ) z
WHERE r <= 3

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-11 : 03:01:47

you didnt specify based on what order you want top 3 rows. Is it based on your PK column value? or do you have any other fields like a date column based on which you want top 3?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-11 : 07:24:23

Here's one way to do it in SQL 2000. It's not very pretty. It uses a loop to determine the RowIDs of the 1st, 2nd, and 3rd record for each UserID, and add them to a temp table. It then uses the temp table to filter the main table. It also makes the assumption that the RowID is the column with which to determine the order of the rows, and that this column is an INT datatype.

CREATE TABLE #tmp (RowID INT)

DECLARE @Count INT SET @Count = 0
WHILE @Count < 3
BEGIN
	INSERT INTO #tmp
	SELECT RowID 
	FROM (	SELECT MIN(RowID) AS RowID, UserID
			FROM tableName
			WHERE RowID NOT IN (SELECT RowID FROM #tmp) 
			GROUP BY UserID) z
	SET @Count = @Count + 1
END

SELECT * 
FROM tableName 
WHERE RowID IN (SELECT RowID FROM #tmp)

DROP TABLE #tmp

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 03:15:23

Also see other methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources