TOP FIVE

How I get top Five salaries ?

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

Try this

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

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

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

of course, I checked it twice ... something is wrong?

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

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.

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;

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

Yes Oraka6 I am 100% agree with Ales, Almost same I have also explained to you before.

EMPNO SALARY
1 100
2 200
3 300
4 400
5 500
6 500
7 700

With rownum you get 500 once, and you don't know which empno.

OK, but the original question doesn't mention empno at all ...

Hi
This will also work

select distinct a.sal from emp a
where 5>=(select count(distinct sal) from emp b where b.sal >=a.sal)
order by a.sal desc

just in place of 5 put what u want the top n salaries

cheers
sarath d