When it comes to SQL Server Interviews one of the most important aspects tested for a candidate is
can the candidate write complex SQL queries?
In order to test the same one of the most common techniques used by the interviewer is by asking
to write a query to find the second highest or the third highest from a given set of numbers.
Normally this question is trailered and put into one employee’s salary context
like find the second highest salary or the third highest salary or the nth highest salary.
We will focus on how to answer this interview question in a proper and elegant way.
Now some of a dev friends would be very confident or super confident they will say
I know this, order by and then do a top
I will manage during the interview I will say don’t do that always go prepared.
Remember a prepared candidate has more chance to get success than an unprepared one.
Everything is fair in getting a job and war, go prepared do not take any risk.
I have this tblEmployee out here and it has an Employee salary I am going to just
first write down this Employee salary out here that we have just focused on the Employee salary.
From this Employee salary I want to find out the second highest salary. This query can we solved
by multiple ways someone can solve it by using row number we can also use co-related queries
or we can use the combination of the top and order by keyword and solve it easily.
Let’s start with a simple solution as interviewer always loves simple solutions. Use the top and
order by and try to get second highest salary. What I will do I will do I will go and I will copy
paste this out here and let’s first plan because once we are able to plan properly how to write
our query we can write and execute it easily. By using simple order by and the top keyword
we want to find the second highest salary from this. Let us see what
kind of logic first we need to write. First will make this as descending I want to find
second highest salary so the first step which goes into my head is make this as descending.
Descending means first there will be 4000 followed by 3000 and 1000.
The second thing is I want to find the second highest salary I will select
the top 2 i.e. 4000 and 3000. First we will do descending then top 2
then will do ascending. Ascending first row is 3000 and 4000 and then after that I will
select the top 1 from. Here select the top one 3000 and 3000 is the second highest salary.
You can see here first we will do descending then we will do a top then we will do ascending
then will select the first-row format the first value format which is 3000
and that’s the final out here. Let’s start writing SQL accordingly. First we
will do descending I will say here order by EmployeeSalary descending once I do descending
you can see I am getting this output here 4000, 3000 and 1000.
Second step is to find second highest salary I will say top 2 and I should get 4000 and 3000.
Then let’s make it ascending and to make it ascending we will use a sub-query. Remember that
the simple solution requires Top, Order by and also a sub-query. Will take this select out here
and I will give you to outer select. So select *from this select
Remember when you are selecting from a select you need to haven alias saying inner query or
something. I will go here and say order by EmployeeSalary asc. You don’t have to write
order by at all by default it is ascending but you know just to make sure that you understand
logic just that it is visible I written it but you don’t have to write this statement because
by default it is ascending. By default, the order by clause is always ascending.
I will still write ascending so that you can see there it is you can see now
that is 3000 and 4000 that is our ascending step and now I will say select the top first from this
and fire, there it is 3000. With that plan it is very easy you can see
here first you made it descending second you use the top keyword after that we made it ascending
and we selected the top 1. If you say find me the third highest salary let’s see that is right get
me the first highest salary 4000. All the test cases are working and looks to be perfect.
During interview it is very difficult to remember these 4 steps specifically
nowadays we have telephonic interviews which has happening and during telephonic interviews
it’s very important that use speak like to the point and properly. Will also show you an
acronym trick how to remember these 4 steps. The first step is Descending, I will take the
D from here. The second step take the top nth whatever is ask by the interviewer T
and then we have ascending then we have One. You can remember this acronym D, T, A, One – “DON’T
Tell Any One”. Descending first,
Top, Ascending, One. This question is further twisted and asked in a deferent way as well. There
can be cross questions around this question and one of the cross questions which definitely comes
is that How can you make this query parametrized? For example now you can see this is the query can
we make it as parameter? Can we take this SQL and put in to the stored procedure and in that
can we give a variable saying give me the second highest salary or give me the first highest salary
so is it possible to give a parameter out here and this query can take a parameter?
At this moment this top and order by it is very difficult to put a parameter out here
because if you see here this the second highest or the third highest this value we are giving
it in the select itself so that means over here we can’t give like this, this is not
allowed. This SQL statement gives an exception inside the select you can’t give a parameter.
You can give a parameter in the var clause and having clause but inside the select you can’t.
One of the things which can come to your mind is let’s a make it a dynamic SQL but the dynamic
SQL Would be a very weird solution. One cross question you know which
comes around this question is that how you can make it parametric?
At this moment the answer to this question is beyond the scope of this video
my goal of this video was to show you the simplest way of finding the nth salary.
I will give a hint over here to do this we need Co-Related Sub- Queries.
We can also use the row number. These are two ways to do it where you can go and you can
put that thing in the var clause. Just iterating some of the
questions which come around this query. First one, how you can make it parametrized?
You can use either Co-Related Sub Queries or you can use the row number and so on.
Another questions which comes is which Queries is faster is the Co-Related Queries faster
or is the order by with the top faster? And the other question which comes around is that
if Co-Related Queries faster or slower how we can improve the performance what are the other
ways improve in the performance? Definitely this is a very
big topic to discuss out here, would encourage you guys to watch my SQL Server interview questions
and answer training where I have covered all such kind of Questions with cross Questions.
How to answer them in detail, there are more then 50+ Questions out here basic once which
can help you during the interview This complete course you can either
get it on questpond.com you can chat without representative or you can subscribe to the
YouTube Membership program so in this same channel we have the YouTube Membership program
where we have C# Interview Questions, SQL Server Interview Questions, ASP.NET Interview Questions,
Angular Interview Questions and Step by Step Videos and you get access to all of them.
I would like to announce small gift before ending this session. If you share this Video
on your twitter, LinkedIn, Facebook any of the social media you get this E-book for free. Share
this Video somewhere on your twitter or Facebook or LinkedIn send as mail questpond@questpond.com
and get this beautiful E-book. Next time when you get a Question
in SQL Server Interviews how to find the top nth salary or the top nth value from a set?
Don’t Tell Any One, remember this. Hit the Subscribe Button. Every time I post interview
question you will get an alerted you are always then brushed-up your skills are brushed-up.
Who knows in your current job you have a problem and this is the channel you know which keep you
on your toes so for yourself subscribe the channel always be ready for Interviews,
always be on your toes, you should always be ready for any kind of disaster.
Thank You Very Much! Happy Job Hunting! Happy Learning!