Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – SELECTing/Displaying Top N rows from a table

To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option.

For example, both of these statements are valid:

USE SqlAndMe

GO

 

SELECT TOP 5 EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SELECT TOP 5 PERCENT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Another way to limit rows in result set is to use SET ROWCOUNT N statement. SET ROWCOUNT N stops processing the query after specified number of rows are returned.

It can be used as below:

USE SqlAndMe

GO

 

SET ROWCOUNT 5

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SET ROWCOUNT 1

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Using SET ROWCOUNT N affects current session, to turn off this setting we need to set it to 0.

SET ROWCOUNT 0

GO

There are some differences between using SET ROWCOUNT N and TOP N [PERCENT]. You can read about these differences here: SET ROWCOUNT VS TOP in SQL Server

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe


Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data

Comments

Leave a comment on the original post [sqlandme.com, opens in a new window]

Loading comments...