Recently in one of the interviews I had given , I was asked question to
write a query to find the employee with min days of leave in previous 3 months department wise.
The table structure was
EMPID | TO_DATE | FROM_DATE | LEAVE _TYPE | DEPT_NO
I was able to write the query
SELECT
min(days) FROM (SELECT id ,(sum((TO_DATE-FROM_DATE)+1) ) days ,dept
FROM emp_leave
WHERE to_date between ADD_MONTHS(sysdate,-3) AND sysdate group by id,dept)
group by dept ;
but when I try to select the emp_id I have to add it in group by statement.
I was stuck there.
I think the query should have been something like
select dept, min(id) keep (dense_rank last order by days)
from ( SELECT id ,
(sum((TO_DATE-FROM_DATE)+1) ) days ,
dept
FROM emp_leave
WHERE to_date between ADD_MONTHS(sysdate,-3)
AND sysdate group by id,dept)
group by dept
;
Well of course, in SQL you have a lot of different way to do this, but when it is about ranking stuff, the first/last function is very useful
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加