To query the first row or the last row in a table is easy-piecey. You simply select the Top 1 row and order the query by ascending or descending. But, how about selecting the second, third or tenth row from a table that meets a certain criteria. That’s when the query gets little tricky.
I’ll show you an easy approach from start to finish. Below is a snapshot of an Employee table with a list of employees and their salaries. The Employee table as you can see is ordered by ID with a list of random salaries.
Employee Table in Descending Order:
To get a maximum salary from the employee table, the MAX() function is used to get the value. Easy eh!
How about if you want to get the second highest salary? How would you shape your query? One way is to write a sub-query like so:
SELECT MAX(Salary) AS 'Max Salary' FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee)
You can also do something like this, although you'll have to do a mental math to write the following query in order to get the second highest salary:
SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP 2 Salary FROM Employee ORDER BY Salary DESC) RESULT ORDER BY Salary
Using DENSE_RANK() function:
WITH RESULT AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS RANK FROM Employee ) Select TOP 1 Salary from RESULT WHERE RANK = 3
The thing to remember is that numbers returned by DENSE_RANK function do not have gaps and invariably use consecutive ranks.
Happy coding 🙂