0

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

9
Contributors
15
Replies
16
Views
8 Years
Discussion Span
Last Post by anubina
0

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

0

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)

0

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.

0

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

0

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

0

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

0

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;

0

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);

Edited by anubina: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.