How to partition the result set in SQL Server

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:

Max Salary:

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)


OR

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

Summary:

The thing to remember is that numbers returned by DENSE_RANK function do not have gaps and invariably use consecutive ranks.

Happy coding πŸ™‚

Resources

Microsoft

About Obi Oberoi

I am Technology Aficionado who has a passion for learning, speaking, blogging and hanging out with geeks and like minded people!
This entry was posted in SQL Server. Bookmark the permalink.