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

avatar image

Ayan Basu
22 3 3 5

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

avatar image

Peso
1.6k 5 6 9

(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

avatar image

RBarryYoung
782 6 9 12

(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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Grant Fritchey ♦♦
137k 20 42 81

(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

avatar image

David 1
1.8k 3 5

(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

SQL Server Central

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

Topics:

x1066

asked: Oct 29, 2009 at 05:28 AM

Seen: 14653 times

Last Updated: Jan 28, 2014 at 11:41 AM

Copyright 2016 Redgate Software. Privacy Policy