question

Ayan Basu avatar image
Ayan Basu asked

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?

t-sql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

what does 'without any SQL keyword' mean?
4 Likes 4 ·
With "3 rd highest salary" do you mean worldwide, company-wide, any age or gender requirements, ... ? A fact without its dimensions is just useless...
1 Like 1 ·
Peso avatar image
Peso answered
SELECT  MIN(Salary)
FROM    (
    	SELECT		DISTINCT TOP(3)	
    			Salary
    	FROM		Table1
    	ORDER BY	Salary
    ) AS d
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bob Hovious avatar image
Bob Hovious answered

On SQL2005 and forward:

;with cte1 as (select Salary, row_number() over(order by salary desc) as rowID from TableX)
 select Salary from cte1 where rowID = 3

I'm also curious about the no "SQL keyword" requirement. What version of MS-SQL are you running?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RBarryYoung avatar image
RBarryYoung answered

The following has no SQL Keywords:

Charlie, what's our third highest salary?

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sharda avatar image
sharda answered
third highest value salary SELECT min(sal) from emp where sal in(select disctinct top 3 sal from emp order by sal DESC);
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user585858 avatar image
user585858 answered
Here are couple of more ways to find second highest salary in SQL
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
To find the 3 highest salary or whatever highest salary Select * from ( select Row_number() over ( order by salary desc ) as Row_INDEX , * From employees ) as Temp where Row_INDEX = 3--replace with what you need furthur more visit: http://sqltechi.blogspot.in/2012/11/miracle-scripts-for-ever-in-sql-server.html in To Find Nth Highest salary from employee category Hope it may help to you
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sannykumar250 avatar image
sannykumar250 answered
select * from Employee_Test as M where (3) =(Select COUNT (Distinct N.Employee_Salary)from Employee_Test as N where N.Employee_Salary>=M.Employee_Salary)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.