GROUP BY and just another column

We all love to create nice queries with GROUP BY to get spectacular data. Then, there is always someone who ask to put there another column... and all comes crashing down...

In Oracle, you usually have to rewrite your query.

Today, helping out a colleague, I found a neat sollution, introduced in 9i (yes, i'm not up to date...)

 

 A typical request from a customer...

"Show me lowest salary for each department..."

for which we can trivially code:

SQL> select deptno, min(sal)   2  from emp   3  group by deptno; 

then the customer adds "...and I need to see employee number as well"

(...)

In the past, this means sub-selects / inline views. But with some new analytic options in 9i, this becomes a trivial

SQL> select deptno, min(sal), min(empno)    2    KEEP ( dense_rank FIRST order by sal) empno   3  from emp   4  group by deptno   5  / 

Read it complete here.

 

 

Here are the 2 options to write the same query:

 

• pre 9i
• SELECT ASSIGNMENT, MAX(EMPLOYEE) KEEP (DENSE_RANK LAST ORDER BY EFFECTIVE) EMPLOYEE, MAX(EFFECTIVE) AS_OF
  FROM ASSIGNMENTS
  GROUP BY ASSIGNMENT;
• post 9i
• SELECT a.assignment, a.employee, a.effective
  FROM assignments a, 
  (SELECT assignment, MAX(effective) as_of FROM assignments GROUP BY assignments) a1
  WHERE a.assignment = a1.assignment
  AND a.effective = a1.as_of ;

Neat.