your answer is right but...
February 13, 2003 - 2pm Central time zone
Reviewer: A reader
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...
Followup February 13, 2003 - 5pm Central time zone:
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.
February 13, 2003 - 10pm Central time zone
Reviewer: Ian from CT
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.
Followup February 14, 2003 - 7am Central time zone:
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
February 16, 2005 - 5am Central time zone
Reviewer: Debashis Payin from India
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
Followup February 16, 2005 - 8am Central time zone:
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
February 22, 2005 - 5am Central time zone
Reviewer: Debashis Payin from India
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
May 2, 2005 - 3am Central time zone
Reviewer: Mandar Sonawane (Mandy) from India
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
Followup May 2, 2005 - 8am Central time zone:
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.
|