x
login about faq Site discussion (meta-askssc)

How to get the 3 rd highest salary without using any SQL keyword?

How to get the 3 rd highest salary without using any SQL keyword?

more ▼

asked Oct 29 '09 at 05:28 AM in Default

Ayan Basu gravatar image

Ayan Basu
22 2 2 3

what does 'without any SQL keyword' mean?

Oct 29 '09 at 06:03 AM Kev Riley ♦♦

With "3 rd highest salary" do you mean worldwide, company-wide, any age or gender requirements, ... ? A fact without its dimensions is just useless...

Feb 14 at 07:49 AM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

9 answers: sort voted first
SELECT  MIN(Salary)
FROM    (
    	SELECT		DISTINCT TOP(3)	
    			Salary
    	FROM		Table1
    	ORDER BY	Salary
    ) AS d
more ▼

answered Oct 29 '09 at 06:20 AM

Peso gravatar image

Peso
1.6k 4 6 8

Oh... Add "DESC" to the ORDER BY clause above...

Oct 29 '09 at 11:15 AM Peso
(comments are locked)
10|1200 characters needed characters left

The following has no SQL Keywords:

Charlie, what's our third highest salary?

Charlie being the head of the payroll department. :-D

more ▼

answered Oct 29 '09 at 10:14 AM

RBarryYoung gravatar image

RBarryYoung
762 5 5 8

I like your answer, it is clean and contains no sql keywords! lol

Dec 11 '09 at 07:29 AM Håkan Winther

It also takes care of the problem with salaries paid in different currencies!

Dec 29 '09 at 07:21 PM Peso
(comments are locked)
10|1200 characters needed characters left

Get the top 1 salary that isn't in the top 2:

select top 1 salary 
from salarytable
where salary not in (select top 2 salary 
                     from salarytable order by salary desc)
order by salary desc

and in case you want the third highest value salary, rather than just the third in a sorted list

select top 1 salary 
from salarytable
where salary not in (select distinct top 2 salary 
                     from salarytable order by salary desc)
order by salary desc

note the distinct on the inner query

more ▼

answered Oct 29 '09 at 06:05 AM

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 38 43 69

(comments are locked)
10|1200 characters needed characters left

Sorry, but I have to ask what you mean by key words. SELECT is a keyword. I think you'll be hard pressed to get the data without it.

How about

SELECT TOP(1) Salary
FROM (SELECT TOP(3) Salary
FROM TableX
ORDER BY Salary DESC)
ORDER BY Salary ASC
more ▼

answered Oct 29 '09 at 08:25 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
62.4k 12 20 66

(comments are locked)
10|1200 characters needed characters left

Maybe you mean you only want to use Standard SQL without T-SQL keywords (such as TOP). For example:

SELECT MAX(Salary) ThirdHighest
FROM Table1 t
WHERE Salary <
(SELECT MAX(Salary)
FROM Table1 t
WHERE Salary <
(SELECT MAX(Salary)
FROM Table1 t));

To be slightly picky, Kev, Grant and Bob's solutions won't necessarily return the third highest salary. They all return the salary from the third row of the table when sorted in salary order. Unless salary is unique that could give a different answer to my query. You decide which meets your actual requirement.

more ▼

answered Oct 29 '09 at 10:10 AM

David 1 gravatar image

David 1
1.8k 1 3

it's fair to be picky.... in this game if you aren't picky then you're sloppy....have added an 'alternative' query to my answer!

Oct 29 '09 at 06:26 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x914

asked: Oct 29 '09 at 05:28 AM

Seen: 8397 times

Last Updated: Feb 14 at 10:50 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.