SQL Server: Tips and Tricks - 2 (2017)

How to get Top / Bottom N Records from SQL Server Table in SQL Server

Scenario:
You are working as SQL Server or TSQL Developer, you need to get top/bottom x rows from a table. How would you do that?

Solution:

You can use TOP clause in your query to return top x records. Top clause is really helpful to take a quick look into some data points instead of returning all the records.

The syntax for top is

Select TOP Number | Percent column1, column2, column3... From SchemaName.TableName

Let's create our sample table dbo.Customer with sample data and try TOP.

--Create dbo.Customer table

Create table dbo.Customer(

Id int,

Name VARCHAR(10),

Address VARCHAR(100))

--Insert records in dbo.Customer table

Insert into dbo.Customer

Values(1,'Aamir','XyZ Address')

,(2,'Raza',Null)

,(3,'Rita','XyZ Address')

,(4,'John','XyZ Address')

,(5,'Lisa','XyZ Address')

1) Let's say if we would like to get Top 3 records from dbo.Customer with all columns, we can use below query.

Select top 3 * from dbo.Customer

The select query with top returned us top 3 records successfully. You can also sort the records and then get top x records by using Top.

2) Sort the records by Name and get top 3 records from dbo.Customer table.

Select top 3 * from dbo.Customer

Order by Name

3) How to get Bottom n Rows from table

As we have seen that we can sort the records first and then get top x rows. If we have a column such as id (auto incremental), sequence number or createdOn (datetime) column, we can sort by desc and then get the top x rows that will return us the bottom rows.

In our case we have Id, if we sort as desc and then get Top 3, we will be able to get bottom 3 records.

Select top 3 * from dbo.Customer

Order by ID desc

4) Use Percent with Top

Let's say if we would like to get 50% of records from dbo.Customer table, we can use percent with Top in our select query. As we have only 5 records in our dbo.Customer table, 50% will be 2.5, 2.5 rows can't be returned so SQL will round to 3 and we will get 3 row.

Select top 50 Percent * from dbo.Customer

Again, Order by clause can be used with Percent the same way we did use with Top Number in example 1, 2 and 3.