Rick Morelan is finest SQL Expert. He is very much known for his excellent book series Joes 2 Pros. His books are not only an inspiration to many who wants to learn SQL Server properly, but a MUST read for any SQL enthusiast. He has written following guest blog post to keep alive the spirit of Interview Questions and Answers Series.
Please read the Introductory Post before continuing reading interview question and answers.
After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in interview, but but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. These questions separated the junior SQL query writers from the advanced SQL query writers. That is because most advanced interview questions combine two or more intermediate level skill sets into 1 solution.
Realizing years ago that just writing and training people for the Microsoft test was not always enough to get them where they wanted to go. Maybe half the time they would get the job so I vowed to do something extra to increase those odds. Perhaps needed to create an entire course on the how to ace SQL query interviews.
Since it seemed 80% of the interview questions were about the advanced queries that is where my focus must be directed. The end result is my SQL Query book on certification (SQL Queries Joes 2 Pros: SQL Query Techniques for Microsoft SQL Server 2008ISBN 1-4392-5318-) doubled in size to nearly 600 pages. In fact this book now adorns more interview prep then certification prep. It’s proven to be a big payoff and my students almost always get the job offer after the interview.
Let’s get on to one of the common interview questions. Most people are fully aware of how the TOP keyword works with a SELECT statement. For example if you wanted to look at the 3 top most expensive products from the [Grant] table of the sample database JProCo then this query would do the trick (note: you can download my sample database from Joes2Pros.com). Watch the video here.
SELECT TOP (3) * FROM [Grant] ORDER BY Amount DESC
The real interview question assumes this is already easy to you and instead they want to find just the 3rd highest Grant. In other words just the record that is in 3rd place by amount. To do this you need rank the records and then predicate for 3rd place. Let’s start off by sorting all the records of the table by highest price.
SELECT * FROM [Grant] ORDER BY Amount DESC
The ORDER BY clause picks what records are listed first based on the field and sort order you pick. In this case we choose amount from highest to lowest. Now we want to use the power of the ORDER BY to pick the rank. Follow the RANK() with OVER() and the put the ORDER BY inside and not the end of the query. Your query and output look like the sample below.
SELECT *, RANK() OVER(ORDER BY Amount DESC) FROM [Grant]
GrantID | GrantName | EmpID | Amount | Rank |
7 | Ben@MoreTechnology.com | 10 | 41000 | 1 |
8 | www.@-Last-U-Can-Help.com | 7 | 25000 | 2 |
9 | Thank you @.com | 11 | 21500 | 3 |
4 | Norman’s Outreach | NULL | 21000 | 4 |
5 | BIG 6’s Foundation% | 4 | 21000 | 4 |
6 | TALTA_Kishan International | 3 | 18100 | 6 |
3 | Robert@BigStarBank.com | 7 | 18100 | 6 |
2 | K_Land fund trust | 2 | 15750 | 8 |
10 | Call Mom @Com | 5 | 7500 | 9 |
1 | 92 Purr_Scents %% team | 7 | 4750 | 10 |
This is very nice but not very neat. It a good idea to alias expression fields to let’s call it GrantRank.
SELECT *, RANK() OVER(ORDER BY Amount DESC) AS GrantRank FROM [Grant]
Almost done right? You can see the 3rd highest grant is Thank you @.com for $21,500. Just 1 critera way from victory. You might think it would be as simple as saying GrantRank = 3. If you try to predicate on a field alias you will discover that the WHERE clause runs before the field expression and you get the following error.
SELECT *, RANK() OVER(ORDER BY Amount DESC) AS GrantRank FROM [Grant] WHERE GrantRank = 3
Msg 207, Level 16, State 1, Line 5
Invalid column name ‘GrantRank’.
GrantRank is not a column until after the query has run so the where clause can’t find what you’re talking about. What can you do? Simply materialize the query like a table by using a Common Table Expression or a Derived Table. Both examples are below.
--Use a Common Table Expression WITH CTE AS (SELECT *, RANK() OVER(ORDER BY Amount DESC) AS GrantRank FROM [Grant] ) SELECT * FROM CTE WHERE GrantRank = 3 --Use a Derived Table SELECT * FROM (SELECT *, RANK() OVER(ORDER BY Amount DESC) AS GrantRank FROM [Grant] ) AS dt WHERE GrantRank = 3
GrantID | GrantName | EmpID | Amount | GrantRank |
9 | Thank you @.com | 11 | 21500 | 3 |
List of all the Interview Questions and Answers Series blogs
Reference: Rick Morelan, Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Pinal,
Thanks for the pointers … You have a typo:
“I encourage all the readers to read her MUST read books Joes 2 Pros.”
fixed and thanks!
Hello…
It is a very nice post but is it not possible to do the same with out using the rank() function?
Hello ss…
Here is a workable solution to Rick’s interview question without using the RANK() function.
/* Create a parameter that sets the position of the
business required value.
**********************************************************************/
DECLARE @nRank int;
SET @nRank = 3;
/* Use the TOP() expression with a derived table to return all
rows that meet the specified business requirement.
**********************************************************************/
SELECT TOP (1) WITH TIES *
FROM [Grant]
WHERE Amount NOT IN ( SELECT TOP (@nRank – 1) WITH TIES Amount
FROM [Grant]
ORDER BY Amount DESC )
ORDER BY Amount DESC;
Hello rstech,
Thank you so much for the reply. I think its easier to use rank() function when there is large amounts of data to be sorted out.
Thank you.
Nice question and a great answer. Thanks for the pointer.
I had to use the ROW_NUMBER(), RANK() AND DENSE_RANK() functions a couple of days ago in a query that’s being used to extract data from a SQL database. Very interesting functions, and very helpful.
Nice article,thank you
Excellent Post Rick!
Pinal bring more guest author like him.
Hi Pinal and Rick,
Thanks for this nice post.
Can you please tell if there is any performance difference between the two methods shown in this post. i.e, between “Use a Common Table Expression” and “Use a Derived Table”.
Thanks.
The following performs much better in large tables by preventing fetching unneeded records:
–Use a Common Table Expression
WITH CTE AS
(SELECT TOP 3 *,
RANK() OVER(ORDER BY Amount DESC) AS GrantRank
FROM [Grant] )
SELECT * FROM CTE
WHERE GrantRank = 3
can we do like this
with cte as ( select top 3 amount from grant order by amount desc ) select min(amount) from cte
Yes. Have you tried it?
Alternatively, You can do it in derived table too
select min(amount) from
(
select top 3 amount from grant order by amount desc
) as t
It’s very simple without using any Rank !!
Thanks, It is very useful and easy to understand…
Cant we use row_number() instead of rank?