SQL SELECT TOP N (most expensive, high salary)
Sunday, October 28, 2007
SQL SELECT TOP N equivalent in ORACLE and MySQL (The most common database/Sql Interview Question)
Something I needed today... I wish this was standarized, but it is not, so here are some examples:
SQL Server:
SELECT TOP 10 product, descr, email
FROM products
ORACLE:
SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10
MySQL:
SELECT product, descr, email
FROM products
LIMIT 10
Now i only want to show the top 5 records how do i do this?
Answer:
Here is the SQL to display the top-n items:
SELECT ENAME, SAL
FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM < 4;
If you are using Oracle 9i you can use the RANK function also.
select *
from (select empno,sal,rank() over (order by sal desc ) rnk from emp)
where rnk <= 5;
thats all! Njoy...
Labels: high salary), MySQL, oracle, SQL Server, Top-n rows (most expensive
posted by MIGHTYMAK @ 10:03 AM,
