1. Junior Member
Join Date
Jun 2001
Posts
56

How I get top Five salaries ?

Salaries
----------
2500
5300
5400
6750
7500
3500
2500
6500
5600

2. aph
Senior Member
Join Date
Jul 2001
Posts
334
Try this

select salaries from emp
where rownum < 6
order by salaries desc;

Join Date
Apr 2001
Location
Czechia
Posts
712
to aph: your query gets first five rows from emp and orders them by salary (rows get their rownums BEFORE ordering):

```
SQL> select salaries from emp;

SALARIES
----------
2500
5300
5400
6750
7500
3500
2500
6500
5600

9 rows selected.

SQL> select salaries from emp
2  where rownum < 6
3  order by salaries desc;

SALARIES
----------
7500
6750
5400
5300
2500

```

to mahmood:

select salaries from
(select salaries from emp order by salaries desc)
where rownum<6

Ales

4. Junior Member
Join Date
Jun 2001
Posts
56

Dear Friends
When u reply, First of all check ur ans: ur self ?
Thanx

Join Date
Apr 2001
Location
Czechia
Posts
712
of course, I checked it twice ... something is wrong?

6. Junior Member
Join Date
Mar 2001
Posts
77
Ales, what happens if there were two salaries belong to the top five list which were exactly the same? Such as:

5300
5300
5400
6750
4500
3500
2500
2300
2600

7. aph
Senior Member
Join Date
Jul 2001
Posts
334
Oraka6
Well, in case of same both salaries, The 2nd salary based on your question Will also consider as a top. There is no problem.

8. There is a problem if you use rownum and salaries are exactly the same.

In 8.1.6 (an d8.1.7 and 9) you can use:

SELECT ename, sal
FROM
( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) as rank
FROM emp
)
WHERE rank <= 5;

In 7, 8 and 8.1.5 you can use:
SELECT e1.ename, e1.sal
FROM emp e1
WHERE 5 > (SELECT COUNT(1) FROM emp e2 WHERE e2.sal > e1.sal)
ORDER BY sal DESC;

Join Date
Apr 2001
Location
Czechia
Posts
712
oraka6, if there were two exactly same salaries that belong to the top five, we would get them both in the result:
```SALARIES
----------
6750
5400
5300
5300
4500```

If we don't want to get them both, we have to use DISTINCT in the inner query.
Both the queries (with or without DISTINCT) could answer the original question since it is a bit vague.

akkerend, what's the problem if we use rownum and salaries are the same? Could you expand on that, please?
Thank you.
Ales

10. aph
Senior Member
Join Date
Jul 2001
Posts
334
Yes Oraka6 I am 100% agree with Ales, Almost same I have also explained to you before.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•