TOP nth salary in SQL Server

If you have ever given or taken an interview then you must have come across to this very commonly asked question “Find top nth salary in SQL”?. Here I will be demonstrating to find the 2nd top highest salary from an employee table.

TOP_N

Lab Script:

Using TOP:

  • Top 2nd employee salary.

Using TOP with MIN():

  • Top 2nd employee salary.

Using TOP with NOT IN:

  • Top 2nd employee salary.

Using TOP with MAX():

  • Top 2nd employee salary.

Using ROW_NUMBER():

  • Top 2nd employee salary.

Using OFFSET: (supported in 2012 and later versions)

  • Top 2nd employee salary.

Using FIRST_VALUE and TOP: (supported in 2012 and 2014)

  • Top 2nd employee salary.

“Now, little tricky.. If you are asked to get TOP nth employee salary with tied value. Means to show all employees having same TOP nth salary. Below are the examples”

Using DENSE_RANK():

  • Top 2nd employee salary with tied data.

Using OFFSET:

  • Top 2nd employee salary with tied data.

Using COUNT():

  • Top 2nd employee salary with tied data.

Using TOP WITH TIES:

  • Top 2nd employee salary with tied data.

PS: If you know any other trick to get TOP nth salary from an employee table, then please please share it up here in the comment section.

One thought on “TOP nth salary in SQL Server

  • June 27, 2016 at 4:52 pm
    Permalink

    For the first four examples, ‘Sunil Desai’ with salary 99000.00 is coming as the result.
    But with ROW_NUMBER(), ‘Hilor Mehta’ with salary 99000.00 is coming as the result. Please correct the code accordingly.

    Reply

Leave a Reply to GeoJul Cancel reply

%d bloggers like this: