i want to get the third highest salary from a table?

Dialect: SQL 92
table is two fields
Emp_id and Emp_salary

How to get third highest salary without using YOP function.

Dialect: T-SQL

SQL:

SELECT top 3 * into #topthree FROM dbo.myTable

ORDER BY Emp_salary DESC

SELECT top 1 * FROM #topthree

ORDER BY Emp_salary

Dialect: SQL 92

SQL:

SELECT TOP 1 * 

    FROM (SELECT TOP 3 * 

             FROM employees 

             ORDER BY salary) Top3

    ORDER BY salary DESC

Dialect: OTHER
On second thought...

What's a YOP function? Did you mean "...without using TOP function."?

Dialect: MS SQL

SQL:

DECLARE @tblTempStorage TABLE (

	[ID] INT IDENTITY(1, 1), 

	Result FLOAT)



INSERT INTO @tblTempStorage (Result)

SELECT DISTINCT

	DEPTH_TO

FROM 

	GB_SAMPLE

ORDER BY

	DEPTH_TO



SELECT 

	* 

FROM 

	GB_SAMPLE

WHERE

	DEPTH_TO = (

							SELECT 

								Result 

							FROM 

								@tblTempStorage

							WHERE

								([ID]  = 3))

Dialect: MySQL

SQL:

SELECT Emp_id, Emp_salary

FROM employees

ORDER BY Emp_salary

LIMIT 2, 1;

Dialect: Oracle
Oracle allows to select pseudo-columns with a special meaning.

SQL:

SELECT *

FROM (

   SELECT ROWNUM as rank, Emp_id, Emp_salary

   FROM employees

   ORDER BY Emp_salary

   )

WHERE rank=3

;

use for any highest salary from the table

SQL:

select min(sal) from (select sal from emp where sal is not NULL order by sal desc) where rownum<=&rownum

I am not sure but it will definitely work just try once.

SQL:

( select salary from <tablename> where sal <(select salary from <tablename> where sal <(select max(sal) from <tablename>))) 

This will give the third highest salary from a table.
And for the second highest salary just remove one phrase that is

SQL:

select salary from <tablename> where salary<(select max(salary) from <tablename>)

SQL:

Select Empname,salary From ##employees A Where 3=(select Count (distinct(salary)) From ##employees B 

Where B.salary>=a.salary)

SQL:

select TOP 1 salary from Employee( select distinct top 3 salary from employee ORDER BY DESC) a ORDER BY salary

SQL:

select salary from <tablename> where salary<(select max(salary) from <tablename> wont work for second highest salary.

The correct query is

SQL:

select max(salary) from <tablename> where salary<(select max(salary) from <tablename>

to get the third highest salary :

SQL:

( select max(salary) from <tablename> where sal <(select max(salary) from <tablename> where sal <(select max(sal) from <tablename>)))

SQL:

select sal from  employees .e where &n=(select distinct(sal) from employees where e.sal<=employees.sal);

SQL:

 select * from emp a where 2=(select count(*) from emp b where a.sal<b.sal);

Consider the Table Salary_TB with 2 columns empname,salary

SQL
----

SQL:

select top 1 (salary) from Salary_TB  where salary< (select max(salary) from Salary_TB where salary<(select max(salary) from Salary_TB ) ) order by salary desc

i think this can understand all to get third highest salary from a table

Anoop.G
3MenTechnologies
9847160368
anoop.gs@gmail.com

y go in for such complex query..try this

SQL:

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top 2 Emp_Sal from Employee_Test order by Emp_Sal desc)

Vishal

:cool