-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|