How to get Top / Bottom N Records from SQL Server Table in SQL Server - SQL Server / TSQL Tutorial Part 113

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


How to get top x rows from a table in SQL Server - SQL Server / TSQL Tutorial


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

How to get Top N Rows from SQL Server Table with order by - SQL Server Tutorial

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


How to get Bottom N rows from table by using Top and Order by in SQL Server



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

How to get Percent Rows from SQL Server Table - SQL Server / TSQL Tutorial

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



No comments:

Post a Comment