your answer is right but...
A reader, February 13, 2003 - 2:59 pm UTC
why are you comparing with 3 , when you want the 4th highest salary...
Tell me the fundamentals of your query
ops$tkyte@8i> select empno, sal
2 from emp a
3 where 3 = ( select count(*)
4 from emp b
Please put in in verbage....i.e. put in in plain english...
February 13, 2003 - 5:20 pm UTC
ops$tkyte@8i> select empno, sal
2 from emp a
3 where 3 = ( select count(*)
4 from emp b
5 where b.sal > a.sal )
6 /
that is something you should strive to do (put it in english). it is what I do when asked to tune a query -- put it in english, verify the requirements and then code a new query based on the question.
But basically that query says:
for every row in emp
count the number of salaries GREATER then that row
when you find a row with only 3 GREATER salaries, you must have the 4th
highest right there
confused ? parsing conventional v/s pl/sql.
Ian, February 13, 2003 - 10:31 pm UTC
When I am creating a trigger , is the compiled code not stored in the database ? Then why would it only parse pl/sql and not the conventional sql.
So that when trigger is actually fired at time of execution it gets parsed statement and executes it.
Also then why is parse not happening in procedure body ? Is the conventional SQL in procedure body parsed and stored.
I guess if you could clarify this in a little detail , this will be totally clear as this applies to all sql code we write.
February 14, 2003 - 7:34 am UTC
The compiled code is stored.
It PARSED the sql (in order to REWRITE the sql).
I'm confused too -- what does this have to do with top-n queries? but anyway when you submit a trigger like:
create trigger t before insert on t for each row
declare
n number;
i number := 5;
begin
select count(*) into n from another_t where x = :new.y and b = i;
end;
a) the code gets compiled into pcode and stored
b) the query gets parsed and rewritten as:
select count(*) from another_t where x = :bv0 and b = :bv1;
(sort of looks like a query you might program in a 3gl doesn't it....)
then, at runtime -- just like YOUR 3gl code would -- the query is parsed by the database and executed.
If we changed the trigger to a procedure -- the SAME steps take place.
You see in Oracle, there is no such thing as "true static sql", all sql is parsed and optimized at runtime, we do not precompute plans and store them
consider what would happen if we did and...
o you added an index - we wouldn't see it, you'd have to recompile
o dropped an index -- we would fail
o analyzed a table for the first time -- we would stay with RBO
o analyzed a table after loading it -- we would still have a plan that thinks the table is empty
and so on....
U can slightly change ur query
Debashis Payin, February 16, 2005 - 5:05 am UTC
Hi Tom
Yes .. u r right .. but u can slightly change ur query to make it more readable(!).. and u won't have to make any note in PLAIN ENGLISH :-)
/************* start query *********************/
SELECT EMPNO,SAL
FROM EMP A
WHERE 4 = ( SELECT COUNT(*)
FROM EMP B
WHERE B.SAL >= A.SAL )
/************* end query *********************/
Thanx
Debashis Payin
February 16, 2005 - 8:13 am UTC
what a bunch of gibberish, get a real keyboard, one that includes all of the letters.
I've no idea what your point is? but I can say "this is not a SMS phone".
Thanks for being patient with me
Debashis Payin, February 22, 2005 - 5:46 am UTC
Hi Tom
Good morning . I am extremely sorry for sending you English written in improper manner . I can assure you that it won't happen in future .
Thanks for being patient with me . Have a nice day .
Debashis Payin
Bangalore
India
Please try for this also
Mandar Sonawane (Mandy), May 02, 2005 - 3:36 am UTC
Hi Debashis and Tom
Whenever there is same salary for any employees,then i think there is problem through ur queries.
According to Debashis
select empno,sal
from emp a
where 10 = ( select count(*)
from emp b
where b.sal >= a.sal
)
/
no rows selected
And according to Tom
select empno, sal
from emp a
where 10 = ( select count(*)
from emp b
where b.sal > a.sal
)
/
no rows selected
So please try for n'th highest through this query
select empno, sal
from emp a
where (n'th - 1) = ( select count(*)
from ( select distinct(sal) from emp ) b
where b.sal > a.sal
)
i.e For 9'th highest use n = 9
select empno, sal
from emp a
where (9 - 1) = ( select count(*)
from ( select distinct(sal) from emp ) b
where b.sal > a.sal
)
/
EMPNO SAL
---------- ----------
7521 1250
7654 1250
May 02, 2005 - 8:26 am UTC
this is why I answered with:
The question is ambigous. the n'th highest WHAT? If you say "salary", find the
person making the N'th highest salary -- then the question is a 'bad' question.
What if you asked for the 4'th highest paid person and your data was
and had examples with duplicates.... the question itself is ambigous.
superb description
A reader, November 02, 2010 - 1:25 pm UTC
Very good description for the Problem. Awesome
n'th max salary
sachin padha, December 01, 2011 - 9:00 am UTC
SELECT * FROM (SELECT persons.*, DENSE_RANK() OVER (ORDER BY salary desc) s_dense_rank FROM persons ) WHERE s_dense_rank = 4