Select 4th highest salary

Question

Post reply

Select 4th highest salary

sachinrshetty

SSCrazy

Points: 2062
More actions
June 23, 2010 at 11:55 pm

#145739

The table structure is
Salary Id
7000 222
5000 333
3600 555
1200 777
6700 888
In an interview I was asked to select 4th highest salary. How to write query for this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Investigate the windowing functions in Books on line

Clear Sky SQL
My Blog[/url]

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

SELECT MIN([Salary]) FROM [SSIS].[dbo].[Salary] where salary in (SELECT DISTINCT TOP 4 [Salary] FROM [SSIS].[dbo].[Salary] ORDER BY [Salary] DESC )

Hope this helps

Raunak J

sachinrshetty SSCrazy Points: 2062 More actions · Answer 3

sachinrshetty

SSCrazy

Points: 2062

June 24, 2010 at 1:27 am

#1184348

Thank u Raunak. Perfect Answer 🙂

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

sachinrshetty (6/24/2010)
Thank u Raunak. Perfect Answer 🙂

You interviewer is not going to say the same to you as Thank you Sachin. Perfect Answer. Be prepared for more detailed questions on the same topic. He may ask you as to What will happen if you have two or more people with same salary? How do you rank them? Do you assign them same rank or different rank.

If you don't prepare for questions like that, you might be stumped. Thats why Dave advised you to have a look at Windowing Functions. There are some functions like ROW_NUMBER(), RANK(), DENSE_RANK() which you can use for different situations. Have a look at these functions if you have time and this will help you to prepare better for the interview.

This is not to say that Raunak solution is wrong. It was a good solution and was new to me. So, Thanks Raunak. But you would be better equiped for the interview if you get into the details a bit.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Raunak Jhawar

SSCoach

Points: 15701

June 24, 2010 at 2:05 am

#1184372

Well said....

Raunak J

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Raunak Jhawar (6/24/2010)
Well said....

+1

Clear Sky SQL
My Blog[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

The query can be even shorter (and faster) if you like:

SELECT MIN([Salary]) FROM (SELECT DISTINCT TOP 4 [Salary] FROM [SSIS].[dbo].[Salary] ORDER BY [Salary] DESC) q

But are you sure that you've been asked to select the 4th highest salary? I would think it is a bit non-life issue. I would ask you to select the employee with the 4th highest salary!

The above approach would not be the best in this case and I would also suggest you to have a look ranking functions...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

[Oops, wrong entry]

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]