SELECT d.department_id , e.last_name, e.salary, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id IN (''''60'''', ''''90'''');
FIRST 功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录 SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值 SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best" FROM employees WHERE department_id in (20,80) ORDER BY department_id, salary;
SELECT department_id, last_name, salary, FIRST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal FROM employees WHERE department_id in(20,30);
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
