how to display only the first 3 rows

[achiman]

Please how can I write a query to display only the top three earners in a table in desc order e.g
Emp Sal
John 100
Jam 120
Abi 90
Jo 170
the query should display
Emp Sal
John 200
Jam 170

thanks

[achiman]

I have tried using numrow but it doesnt do the job

[achiman]

please is there any function called rank in oracle and how can i use it

[achiman]

rank does not work maybe because im using oracle8i

[debasisdas]

Try this

select * from emp where rownum<4 order by sal desc

[manuhar]

ya what debasisdas wrote works for ur query

[anubina]

Try this

select * from emp where rownum<4 order by sal desc

Hello,

Before solution is incorrect.

The good solution:

SELECT *
   FROM (SELECT *
                 FROM emp
               ORDER BY sal DESC)
 WHERE ROWNUM <= 3

First step - Obtain the records with all fields in the correct order (view-inline)
Second step - Cut the records in the 3 (Top1-query)

[Kishor_12]

I hope this query also may help:

Select top(3) from emp order by sal DESC

[cgyrob]

There is no Top in Oracle. That is SQL Server syntax.

[debasisdas]

I hope this query also may help:

Select top(3) from emp order by sal DESC

You need to read question properly before answering to them. This is oracle forum not sql server.

[abidns]

select sal from employ where rownum<4

This will 100% work

[Ramesh S]

The good solution:

SELECT *
   FROM (SELECT *
                 FROM emp
               ORDER BY sal DESC)
 WHERE ROWNUM <= 3

What anubina wrote is correct.

The syntax is

SELECT *
  FROM (SELECT * FROM table_name ORDER BY col_name DESC)
 WHERE ROWNUM <= N;

Check this link

[anubina]

select sal from employ where rownum<4

This will 100% work

The defect of this query is the ORDER.

For guarantee the first 3 rows, you need pre-ordering the rows (all rows) and order by the field required. Remember the problem:

Obtain first 3 rows in Desc Order:
Emp Sal ROWNUM
John 100 1
Jam 120 2
Abi 90 3
Jo 170 4

In this order of insertion of rows, the simple query not work, because you can't order by "Sal", if you use:
select * from employees where rownum <= 3 ORDER BY Sal Desc
Cut in the 3 row (Abi 90) and it is an ERROR.

If you use a view-inline, you can Order by the field "Sal", and then the cursor (with the rows) It propagates to the Top1-Query where it will filter in the specified row in the correct order

[cgyrob]

@anubina why are you re-treading old threads.

You already provided the inline view solution for this thread so why are you re-iterating your own solution?

[yogeshrachcha]

This query works perfect for your requirements.
Try using this on a large database and you will see the efficiency.

SELECT *
FROM emp
WHERE sal IN (
SELECT sal
FROM (
SELECT DISTINCT sal
FROM emp
ORDER BY sal DESC
)
WHERE rownum <= 3
)
ORDER BY sal desc;

[anubina]

This query works perfect for your requirements.
Try using this on a large database and you will see the efficiency.

SELECT *
FROM emp
WHERE sal IN (
SELECT sal
FROM (
SELECT DISTINCT sal
FROM emp
ORDER BY sal DESC
)
WHERE rownum <= 3
)
ORDER BY sal desc;

WITH
 lock_top3sal AS (
SELECT sal
  FROM (SELECT DISTINCT sal FROM emp ORDER BY sal DESC)
 WHERE ROWNUM <= 3
)
SELECT *
  FROM emp
 WHERE sal IN (SELECT sal FROM lock_top3sal);