i want to get the third highest salary from a table? - Page 2 - SQL Recipes a FREE cookbook of SQL queries and examples

#11 (**permalink**) 04-22-2008, 06:39 AM

SELECT TOP 1 salary FROM Employee( SELECT DISTINCT top 3 salary FROM employee ORDER BY DESC) a ORDER BY salary

#12 (**permalink**) 04-24-2008, 10:22 AM

Quote:

Originally Posted by lavanya

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

( 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

SELECT salary FROM <tablename> WHERE salary<(SELECT max(salary) FROM <tablename>)

SELECT salary FROM <tablename> WHERE salary<(SELECT max(salary) FROM <tablename> wont work FOR second highest salary.

The correct query is
SELECT max(salary) FROM <tablename> WHERE salary<(SELECT max(salary) FROM <tablename>

to get the third highest salary :

( SELECT max(salary) FROM <tablename> WHERE sal <(SELECT max(salary) FROM <tablename> WHERE sal <(SELECT max(sal) FROM <tablename>)))

#13 (**permalink**) 04-24-2008, 12:20 PM

SELECT sal FROM employees .e WHERE &n=(SELECT DISTINCT(sal) FROM employees WHERE e.sal<=employees.sal);

#14 (**permalink**) 04-30-2008, 12:51 PM

Quote:

Originally Posted by Prashant Yadav

table is two fields
Emp_id and Emp_salary

How to get third highest salary without using YOP function.

SELECT * FROM emp a WHERE 2=(SELECT count(*) FROM emp b WHERE a.sal<b.sal);

#15 (**permalink**) 05-06-2008, 08:32 AM

Consider the Table Salary_TB with 2 columns empname,salary

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

#16 (**permalink**) 05-20-2008, 12:02 PM

y go in for such complex query..try this

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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

User Name		Remember Me?
Password