Find second highest salary in database

Query 1
select max(sal) from emp where sal <(select max(sal) from emp);
Query 2
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee );
Query 3
SELECT salary FROM Employee
ORDER BY salary DESC
LIMIT 1,1;
Query 4
SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp
ORDER BY Salary;
Query 5
select top 1 sal from (select top 2 sal from emp order by sal desc) as emp order by sal;
Query 6
select min(sal) as sal from (select top 2 sal from emp order by sal desc) as emp;

well, if I want to find top 50th sal from emp table. what should I do?
Here comes the benefit of Ranking in Database which is one of the advanced aggregate functions.

select * FROM (
select EmpID, Salary
,rank() over (order by Salary DESC) emprank
from emp
)
WHERE emprank = N;

If the ORDER BY clause cannot distinguish between two rows, it will give them different rankings (randomly).
RANK() and DENSE_RANK() will give the same ranking to rows that cannot be distinguished by the ORDER BY clause
DENSE_RANK() will always generate a contiguous sequence of ranks (1,2,3,...), whereas RANK() will leave gaps after two or more rows with the same rank ( if two students win the gold medal, there is no second place, only third).

Detailed Example

CREATE TABLE PRODUCTS
(
       PRODUCT_ID     INTEGER,
       PRODUCT_NAME   VARCHAR(30)
);
CREATE TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       YEAR           INTEGER,
       Quantity       INTEGER,
       PRICE          INTEGER
);       

INSERT INTO PRODUCTS VALUES ( 100, 'Santoor');
INSERT INTO PRODUCTS VALUES ( 200, 'Sandal');
INSERT INTO PRODUCTS VALUES ( 300, 'Lux');
INSERT INTO PRODUCTS VALUES ( 400, 'Medimix');

INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 6000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 6000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 8,  6000);
INSERT INTO SALES VALUES ( 4, 200, 2010, 10, 9000);
INSERT INTO SALES VALUES ( 5, 200, 2011, 15, 9000);
INSERT INTO SALES VALUES ( 6, 200, 2012, 20, 9000);
INSERT INTO SALES VALUES ( 7, 300, 2010, 20, 8000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 8000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 8000);

Write a query to select the top product sold in each year? 

Finding rank for each product in each year
Query 1
step 1
SELECT P.PRODUCT_NAME,
       S.YEAR,s.Quantity,
       RANK() OVER (
              PARTITION BY S.YEAR 
              ORDER BY S.QUANTITY DESC
              ) PRODRNK
FROM   PRODUCTS P,
       SALES S
WHERE  P.PRODUCT_ID = S.PRODUCT_ID

step 2

SELECT PRODUCT_NAME,
       YEAR
FROM
(
SELECT P.PRODUCT_NAME,
       S.YEAR,s.Quantity,
       RANK() OVER (
              PARTITION BY S.YEAR 
              ORDER BY S.QUANTITY DESC
              ) PRODRNK
FROM   PRODUCTS P,
       SALES S
WHERE  P.PRODUCT_ID = S.PRODUCT_ID
) Details
WHERE PRODRNK = 1;

Query 2 (without ranking function)
step 1
SELECT YEAR,
MAX(QUANTITY) QUAN
FROM SALES
GROUP BY YEAR
step 2
SELECT P.PRODUCT_NAME,
S.YEAR,
S.QUANTITY
FROM
(
SELECT YEAR,
MAX(QUANTITY) QUAN
FROM SALES
GROUP BY YEAR
)maximum, SALES S,
PRODUCTS P
WHERE maximum.YEAR = S.YEAR
AND maximum.QUAN = S.QUANTITY
AND S.PRODUCT_ID = P.PRODUCT_ID;