x

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, 2009 at 05:28 AM in Default

Ayan Basu gravatar image

Ayan Basu
22 3 3 3

what does 'without any SQL keyword' mean?
Oct 29, 2009 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, 2013 at 07:49 AM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

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

answered Oct 29, 2009 at 06:20 AM

Peso gravatar image

Peso
1.6k 5 6 8

Oh... Add "DESC" to the ORDER BY clause above...
Oct 29, 2009 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, 2009 at 10:14 AM

RBarryYoung gravatar image

RBarryYoung
782 5 5 8

I like your answer, it is clean and contains no sql keywords! lol
Dec 11, 2009 at 07:29 AM Håkan Winther
It also takes care of the problem with salaries paid in different currencies!
Dec 29, 2009 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, 2009 at 06:05 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(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, 2009 at 08:25 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(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, 2009 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, 2009 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986

asked: Oct 29, 2009 at 05:28 AM

Seen: 11754 times

Last Updated: Jan 28 at 11:41 AM