How to make aggregate function work on non-sort key

原创于 2011-10-10 00:45:30 发布 · 113 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

本文介绍了一种使用SQL的FIRST和LAST函数来替代自连接或视图的方法，这种方法可以提高查询性能。通过具体示例展示了如何利用这些函数从已排序的数据组中选取特定值。

"When you need a value from the first or last row of a sorted group, but the needed

value is not the sort key, the FIRST and LAST functions eliminate the need for

self-joins or views and enable better performance."

SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
   FROM employees
   GROUP BY department_id;

From Oracle Document