ORDER BY (OFFSET-FETCH and WITH TIES) - SQL 2012

This post is about ORDER BY and the relevant functions: In here I don’t write the details in the text because the screen shots will make clear all.

TOP 3 or Top (3)

TOP (1) PERCENT

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 takes 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 retrieved.

Filtering Data with OFFSET-FETCH:

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

Practicable example for OFFSET-FETCH:

you’re implementing a paging concept where you return to the user one page of rows at a time. The user passes as input parameters to your procedure or a function the page number they are after (@pagenum parameter) and page size (@pagesize parameter). This means that you need to skip as many rows as @pagenum minus one times @pagesize, and fetch the next @pagesize rows. This can be implemented using the following code (using local variables for simplicity).

Cheers!