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