Bits of .NET

In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well.

SELECT
   <column(s)>
FROM
   <table(s)>
ORDER BY
   <sort column(s)>
OFFSET 1 ROWS   -- Skip this number of rows
FETCH NEXT 1 ROWS ONLY;  -- Return this number of rows

Note that this query template requires “ORDER BY” clause.
If you don’t want to use that clause then you can write it as – Order by (Select NULL)  

Here is another example: Limit with offset to select 11 to 20 rows in SQL Server.

SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

• ORDER BY: required
• OFFSET: optional number of skipped rows
• NEXT: required number of next rows

 

You can use ;with as second options:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

The query result format is in the following example.

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

;with myTableWithRowNumbers AS(select row_number() over(order by salary DESC) as 'row', salary from Employee GROUP BY salary)
select salary as SecondHighestSalary from myTableWithRowNumbers where row = 2