SecondLargest
How to find the second largest?
First way:
SELECT MAX(col)
FROM table
WHERE col < (SELECT MAX(col) FROM table)
Second way:
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);
Find the nth Largest?
1. Use rownum for Oracle
select sal from
(select rownum rn,b.sal from
(select unique sal from emp a order by sal desc) b
)
where rn=3
2. One may use TOP command in SQL Server.
3. Another way:
SELECT * FROM tbl_Employee a WHERE
3 = (SELECT count(DISTINCT b.Salary)) FROM tbl_Employee b WHERE a.Salary <= b.Salary);