Login or Sign Up to become a member!

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Return Top N Rows

From Wiki

Jump to: navigation, search

You want to give users the ability to return 5, 10, 20 or any number of rows where they can supply the number. In SQL Server 2005 you can now use TOP with a variable. IN SQL Server 2000 you have to use SET ROWCOUNT

SQL Server 2000 version.

Create this table

tsqlLine number Off | Hide | Select all

CREATE TABLE #TopTest(SomeValue VARCHAR(53))
INSERT #TopTest VALUES(1)
INSERT #TopTest VALUES(2)
INSERT #TopTest VALUES(3)
INSERT #TopTest VALUES(4)
INSERT #TopTest VALUES(5)
INSERT #TopTest VALUES(6)
INSERT #TopTest VALUES(7)
INSERT #TopTest VALUES(8)

Return the top 5 rows ascending

tsqlLine number Off | Hide | Select all

DECLARE @TOP INT
SELECT @TOP =5
SET ROWCOUNT @TOP
SELECT *
FROM #TopTest
ORDER BY SomeValue
SET ROWCOUNT 0

Return the top 5 rows descending

tsqlLine number Off | Hide | Select all

DECLARE @TOP INT
SELECT @TOP =5
SET ROWCOUNT @TOP
SELECT *
FROM #TopTest
ORDER BY SomeValue DESC
SET ROWCOUNT 0

Make sure that you set the ROWCOUNT back to 0 after the query because it might mess up later statements

SQL Server 2005 version.

First create this table

tsqlLine number Off | Hide | Select all

CREATE TABLE #TopTest(SomeValue VARCHAR(53))
INSERT #TopTest VALUES(1)
INSERT #TopTest VALUES(2)
INSERT #TopTest VALUES(3)
INSERT #TopTest VALUES(4)
INSERT #TopTest VALUES(5)
INSERT #TopTest VALUES(6)
INSERT #TopTest VALUES(7)
INSERT #TopTest VALUES(8)

Return the top 5 rows descending

tsqlLine number Off | Hide | Select all

DECLARE @TOP INT
SELECT @TOP =5
SELECT TOP (@TOP) *
FROM #TopTest
ORDER BY SomeValue

Return the top 5 rows descending

tsqlLine number Off | Hide | Select all

DECLARE @TOP INT
SELECT @TOP =5
SELECT TOP (@TOP) *
FROM #TopTest
ORDER BY SomeValue DESC

Contributed by: --SQLDenis 02:09, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Sorting, Limiting

Currently 2.22/5
1
2
3
4
5

128 Rating: 2.2/5 (9 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Return_Top_N_Rows"