Friday, September 11, 2009

HOW TO SHOW BIGGEST FIVE / TOP-N QUERY

SELECT ROWNUM AS RANK, last_name, salary
FROM (SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;



SELECT e1.RANK, e.NAME, e.dige, e.salary
FROM ha e,
(SELECT ROWNUM RANK, salary
FROM (SELECT DISTINCT salary
FROM ha
ORDER BY salary DESC)
WHERE ROWNUM <= 4) e1
WHERE e.salary = e1.salary
ORDER BY salary DESC


SELECT f.RANK, e.NAME, e.dige, e.salary
FROM ha e,
(SELECT ROWNUM RANK, salary
FROM (SELECT DISTINCT salary
FROM ha
ORDER BY salary DESC)
WHERE ROWNUM <= 4) f
WHERE e.salary = f.salary
ORDER BY salary DESC

No comments: