I hope you can help me with my homework :)
We need to build a query that outputs the top N best paid employees.
My version works perfectly fine.
For example the top 3:
SELECT name, salary
FROM staff
WHERE salary IN ( SELECT *
FROM ( SELECT salary
FROM staff
ORDER BY salary DESC )
WHERE ROWNUM <= 3 )
ORDER BY salary DESC
;
Note that this will output employees that are in the top 3 and have the same salary, too.
1: Mike, 4080
2: Steve, 2800
2: Susan, 2800
2: Jack, 2800
3: Chloe, 1400
But now our teacher does not allow us to use ROWNUM.
I searched far and wide and didn't find anything useable.
My second solution thanks to Justin Caves' hint.
First i tried this:
SELECT name, salary, ( rank() OVER ( ORDER BY salary DESC ) ) as myorder
FROM staff
WHERE myorder <= 3
;
The errormessage is: "myorder: invalid identifier"
Thanks to DCookie its now clear:
"[...] Analytics are applied AFTER
the where clause is evaluated, which
is why you get the error that myorder
is an invalid identifier."
Wrapping a SELECT around solves this:
SELECT *
FROM ( SELECT name, salary, rank() OVER ( ORDER BY salary DESC ) as myorder FROM staff )
WHERE myorder <= 3
;
My teacher strikes again and don't allow such exotic analytic functions.
3rd solution from @Justin Caves.
"If analytic functions are also
disallowed, the other option I could
imagine-- one that you would never,
ever, ever actually write in practice,
would be something like"
SELECT name, salary
FROM staff s1
WHERE (SELECT COUNT(*)
FROM staff s2
WHERE s1.salary < s2.salary) <= 3
See Question&Answers more detail:
os 
