# TOP FIVE

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 08-24-2001, 08:18 AM
mahmood

How I get top Five salaries ?

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

• 08-24-2001, 08:50 AM
aph
Try this

select salaries from emp
where rownum < 6
order by salaries desc;
• 08-24-2001, 09:18 AM
ales
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

• 08-24-2001, 09:24 AM
mahmood

Dear Friends
When u reply, First of all check ur ans: ur self ?
Thanx
• 08-24-2001, 09:29 AM
ales
of course, I checked it twice ... something is wrong?
• 08-24-2001, 12:57 PM
oraka6
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

• 08-24-2001, 02:09 PM
aph
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.

• 08-24-2001, 05:23 PM
akkerend
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;
• 08-27-2001, 05:09 AM
ales
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

• 08-27-2001, 09:08 AM
aph
Yes Oraka6 I am 100% agree with Ales, Almost same I have also explained to you before.
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last