Member Avatar for 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

  • 9 Contributors
  • 15 Replies
  • 366 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by anubina

Dani AI

Generated

The sample in the first post is inconsistent (the input rows show single salaries but the “expected” output shows larger totals). Two different problems are commonly conflated here: (A) return the top N rows by salary, or (B) return the top N employees by total pay when an employee can have multiple salary rows. The thread correctly highlights the ROWNUM/order-by pitfall, and also shows DISTINCT/IN and WITH workarounds; what’s missing are clear, version-aware alternatives and tie-handling options.

For modern Oracle (analytic functions or 12c+), the solutions are clearer and more controllable. Example — top 3 rows by salary using an analytic rank:

-- top 3 rows by salary (analytic)
SELECT emp, sal
FROM (
  SELECT emp, sal,
         ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn
  FROM emp
)
WHERE rn <= 3;

Or, on Oracle 12c+ the SQL-standard shortcut:

-- Oracle 12c+ simple form
SELECT emp, sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 3 ROWS ONLY;

If the real goal is “top 3 employees by total pay” (sum per employee), group first and then rank the totals:

-- top 3 employees by total salary (group then rank)
SELECT emp, total_sal
FROM (
  SELECT emp, total_sal,
         ROW_NUMBER() OVER (ORDER BY total_sal DESC) AS rn
  FROM (
    SELECT emp, SUM(sal) AS total_sal
    FROM emp
    GROUP BY emp
  )
)
WHERE rn <= 3;

To include ties (everyone who shares a top‑3 total), replace ROW_NUMBER() with DENSE_RANK() or RANK() as required.

Notes and troubleshooting: as and pointed out, ordering must happen before limiting — that’s why the naive use of ROWNUM with a trailing ORDER BY fails. ’s initial query demonstrates that pitfall. ’s TOP syntax is SQL Server (corrected by ). and showed DISTINCT/IN and WITH approaches; analytic functions or FETCH often give clearer behavior and simpler tie/aggregation control on newer Oracle releases. For large tables, ensure appropriate indexing on the ORDER BY column and test plans when performance matters.

Recommended Answers

All 15 Replies

Member Avatar for achiman

I have tried using numrow but it doesnt do the job

Member Avatar for achiman

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

Member Avatar for achiman

rank does not work maybe because im using oracle8i

Member Avatar for debasisdas

Try this

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

ya what debasisdas wrote works for ur query

Member Avatar for 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)

Member Avatar for Kishor_12

I hope this query also may help:

Select top(3) from emp order by sal DESC

Member Avatar for cgyrob

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

Member Avatar for 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.

Member Avatar for abidns

select sal from employ where rownum<4

This will 100% work

Member Avatar for 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

Member Avatar for 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

Member Avatar for cgyrob

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?

Member Avatar for 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;

Member Avatar for 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);
Edited by anubina because: n/a
Be a part of the DaniWeb community

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