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

Recommended Answers

Try this

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

ya what debasisdas wrote works for ur query

Jump to Post

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 …

Jump to Post

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

Jump to Post

All 15 Replies

I have tried using numrow but it doesnt do the job

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

rank does not work maybe because im using oracle8i

Try this

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

ya what debasisdas wrote works for ur query

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)

I hope this query also may help:

Select top(3) from emp order by sal DESC

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

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.

select sal from employ where rownum<4

This will 100% work

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

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

@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?

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;

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);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.