I'm new to working with analytic functions.
DEPT EMP SALARY ---- ----- ------ 10 MARY 100000 10 JOHN 200000 10 SCOTT 300000 20 BOB 100000 20 BETTY 200000 30 ALAN 100000 30 TOM 200000 30 JEFF 300000
I want the department and employee with minimum salary.
Results should look like:
DEPT EMP SALARY ---- ----- ------ 10 MARY 100000 20 BOB 100000 30 ALAN 100000
EDIT: Here's the SQL I have (but of course, it doesn't work as it wants staff in the group by clause as well):
SELECT dept, emp, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) FROM mytable GROUP BY dept
I think that the Rank() function is not the way to go with this, for two reasons.
Firstly, it is probably less efficient than a Min()-based method.
The reason for this is that the query has to maintain an ordered list of all salaries per department as it scans the data, and the rank will then be assigned later by re-reading this list. Obviously in the absence of indexes that can be leveraged for this, you cannot assign a rank until the last data item has been read, and maintenance of the list is expensive.
So the performance of the Rank() function is dependent on the total number of elements to be scanned, and if the number is sufficient that the sort spills to disk then performance will collapse.
This is probably more efficient:
select dept,
emp,
salary
from
(
SELECT dept,
emp,
salary,
Min(salary) Over (Partition By dept) min_salary
FROM mytable
)
where salary = min_salary
/
This method only requires that the query maintain a single value per department of the minimum value encountered so far. If a new minimum is encountered then the existing value is modified, otherwise the new value is discarded. The total number of elements that have to be held in memory is related to the number of departments, not the number of rows scanned.
It could be that Oracle has a code path to recognise that the Rank does not really need to be computed in this case, but I wouldn't bet on it.
The second reason for disliking Rank() is that it just answers the wrong question. The question is not "Which records have the salary that is the first ranking when the salaries per department are ascending ordered", it is "Which records have the salary that is the minimum per department". That makes a big difference to me, at least.