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;
