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
: requiredOFFSET
: optional number of skipped rowsNEXT
: 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