CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Peter Lang
Peter Lang

Reputation: 55524

Getting lowest values in GROUP BY

WITH test_data AS (
  SELECT 1 key, 1 a, 2 b FROM dual UNION ALL  --# Lowest a for key=1
  SELECT 1 key, 2 a, 1 b FROM dual UNION ALL
  SELECT 2 key, 3 a, 3 b FROM dual UNION ALL  --# Lowest a for key=2, lowest b
  SELECT 2 key, 3 a, 4 b FROM dual UNION ALL
  SELECT 2 key, 4 a, 5 b FROM dual
)

I'm trying to group by key, and retrieve the lowest a and the corresponding b (lowest b in case of ties), plus the SUM of its bs:

       KEY          A    FIRST_B      SUM_B
---------- ---------- ---------- ----------
         1          1          2          3
         2          3          3         12

I can realize this with a sub-select

SELECT key, MIN(a) AS a, first_b, SUM(b) AS sum_b
FROM (
  SELECT key, a, b,
         FIRST_VALUE(b) OVER (PARTITION BY key ORDER BY a, b) AS first_b
  FROM test_data
)
GROUP BY key, first_b

but I wonder if there is a way to avoid the sub-select, something like

SELECT key, a, SUM(b) AS sum_b,
       MIN( FIRST_VALUE(b) OVER (PARTITION BY key ORDER BY a, b) ) AS first_b
FROM test_data
GROUP BY key, a

which raises ORA-30483: window functions are not allowed here

Thanks in advance, Peter

Upvotes: 1

Views: 680

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146449

I think you'd probably also call the idea below a sub-select (or 2 subselects), but try it anyway.
Otherwise you're out of luck, as what you want requires that you perform two different group by aggregations, one on the key alone, and one on the key and on the a attribute. And you cannot do two different group by aggregations in the same SQL statement. So, you need at least two different SQL statements, which must then obviously be combined in some way. And there is no way to combine two resultsets from two different SQL statments into one without treating one or the other (or both) as a sub-select of one kind or another)

   Select X.key, X.a, y.firstBm X.sum_b  
   From
    (SELECT tda.key, MIN(tda.a) a, 
        Min(case tdb.b) firstB, 
        SUM(tda.b) sum_b 
       FROM test_data tda
       Group By key) X
    Left Join
      (Select tda.key, a, 
        Min(b) firstB
       FROM test_data     
       Group By key, a) Y
    On Y.key = X.key  
       And Y.a = X.a

Upvotes: 0

Mike Meyers
Mike Meyers

Reputation: 2895

Are you looking for the first/last aggregate functions? Because of the MIN function, ordering by B on the first_b column should be redundant but that probably depends on how exactly you intend on using it.

WITH test_data AS (
  SELECT 1 key, 1 a, 2 b FROM dual UNION ALL  --# Lowest a for key=1
  SELECT 1 key, 2 a, 1 b FROM dual UNION ALL
  SELECT 2 key, 3 a, 3 b FROM dual UNION ALL  --# Lowest a for key=2, lowest b
  SELECT 2 key, 3 a, 4 b FROM dual UNION ALL
  SELECT 2 key, 4 a, 5 b FROM dual
)
select 
  key, 
  min(a) as a,
  min(b) keep (dense_rank first ORDER BY a, b) as first_b,
  SUM(b) AS sum_b
FROM test_data
GROUP BY key

Upvotes: 4

Related Questions