How to get the 3 rd highest salary without using any SQL keyword?
SELECT MIN(Salary) FROM ( SELECT DISTINCT TOP(3) Salary FROM Table1 ORDER BY Salary ) AS d
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
distinct on the inner query
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.
SELECT TOP(1) Salary FROM (SELECT TOP(3) Salary FROM TableX ORDER BY Salary DESC) ORDER BY Salary ASC
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?
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.
The following has no SQL Keywords:
Charlie, what's our third highest salary?
Charlie being the head of the payroll department. :-D
6 People are following this question.