ORDER BY - T-SQL / SQL in SQL Server

This post is about ORDER BY function and its different functionality.

1. TOP n or Top (n)

2. TOP (n) PERCENT

3. WITH TIES

4.OFFSET-FETCH

TOP n or Top (n): This will return top n records.

TOP (n) PERCENT: This will return n% of the records, for example the below example shows that total record 830, so 1 % of 830 ~ 9 records.

This example shows that how to use TOP function dynamically: TOP (@n)

WITH TIES: It will retrieve the TOP (n) records + get the same order by field records as well:

In this 2nd query, even we try to retrieve top 3 records; third records order date is same as 4th records so it returns that as well. But, in third query Order By (order date and order id), 3rd and 4th records are not same order id, so only 3 records returns.

Filtering Data with OFFSET-FETCH:

Skipping capability, in this below query 2 record skip and take next 4 records

Cheers!