Common Question on Forums, how to get the 3rd highest salary or similar. Also a common job interview question perhaps?
I am using the AdventureWorksDW2012 database. Tests were done with SQL Server 2012 SP2
I run each query twice. Once with the base table and once with a covering Index.
Here is the covering Index:
CREATE NONCLUSTERED INDEX _E_DimEmployee_BaseRate
ON [dbo].[DimEmployee] ([BaseRate])
INCLUDE ([FirstName],[LastName])
Correlated subquery:
SELECT FirstName, LastName, BaseRate
FROM DimEmployee e
WHERE (SELECT COUNT(DISTINCT BaseRate)
FROM DimEmployee p WHERE e.BaseRate <= p.BaseRate) = 4
Why is this a good answer? It’s not really but this will work on any SQL implementation.
It’s fairly slow, it will do a lot of look ups. The subquery is evaluated every time a row is processed by the outer query. This query uses dense ranking and can return multiple rows.
Here are the IO and time stats.
Without Index:
Table ‘Worktable’. Scan count 564, logical reads 1337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimEmployee’. Scan count 54, logical reads 2646, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 20 ms.
With covering Index:
Table ‘Worktable’. Scan count 349, logical reads 907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimEmployee’. Scan count 54, logical reads 233, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 13 ms.
Double Order By with TOP statement:
SELECT TOP 1 FirstName, LastName, BaseRate
FROM ( SELECT TOP 4 FirstName, LastName, BaseRate
FROM DimEmployee ORDER BY BaseRate DESC) AS MyTable
ORDER BY BaseRate ASC;
Why is this a good answer? Because it is an easy syntax to remember.
Let’s look at the subquery, which returns the N highest salaries in the DimEmployee table in descending order. Then, the outer query will re-order those values in ascending (default) order, this means the Nth highest salary will now be the topmost salary. Keep in mind that the TOP statement is MS SQL server specific. MySQL would use LIMIT 1 for instance. In addition this solution cannot do DENSE ranking and only returns one row even if two employees share the same BaseRate.
Edit June 2015: The addition of LIMIT/OFFSET on SQL Server 2012 made answer obsolete. The syntax for LIMIT/OFFSET has been added on the bottom of this post.
Here are the IO and time stats.
Without Index:
Table ‘DimEmployee’. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 5 ms.
With covering Index:
Table ‘DimEmployee’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 0 ms.
Use Windowing function:
SELECT FirstName, LastName, BaseRate
FROM (SELECT FirstName, LastName, BaseRate, DENSE_RANK() OVER (ORDER BY BaseRate DESC) Ranking
FROM DimEmployee) AS MyTable
WHERE Ranking = 4
Why is this a good answer? Because it performs the best – performance is king. The Syntax is also ANSI SQL however of the “Big 3” only Oracle and MS are using it. In addition you can chose to use ROW_NUMBER, DENSE_RANK or regular RANK.
Here are the IO and time stats.
Without Index:
Table ‘DimEmployee’. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 2 ms.
With covering Index:
Table ‘DimEmployee’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 0 ms.
Edit: June 2015
Use OFFSET (SQL Server 2012):
SELECT FirstName, LastName, BaseRate
FROM DimEmployee e
ORDER BY BaseRate DESC
OFFSET 3 ROWS
FETCH NEXT 1 ROWS ONLY
Why is this a good answer? Because similar Syntax exists on the other platforms.
Performance wise it runs as well as the windowing solution.
fullparam 