ORACLE SQL: select MAX value based on 2 different variables (group by function)

Question

I think my problem is quite simple but I don´t really get it :)

I'm using SQL Developer as IDE and have a large table which looks like this:

ID	technology	speed
1	3G	20
1	2G	10
1	4G	40
1	5G	100
2	3G	60
2	4G	90
2	5G	150
3	3G	30
3	4G	50

I need the max value of 'technology' for each 'ID' and also need the 'speed' in the result:

ID	technology	speed
1	5G	100
2	5G	150
3	4G	50

my SQL looks like that:

SELECT ID, MAX(technology) AS technology, speed
  FROM "table"
 GROUP BY ID, speed;

but with this SQL I get multiple selections for each ID

any ideas?

score 1 · Answer 1 · answered Mar 30 '22 at 17:45

Since you're including speed in your select and group, and those values vary per row, your current query will basically return the full table just with the MAX(technology) for each row. This is because speed can't be grouped into a single value as they are all different.

ie.

ID  technology  speed
1   5G  20
1   5G  10
1   5G  40
1   5G  100

Based purely on your sample set, you could select the MAX(speed) since it always coincides with the MAX(technology), and then you would get the right results:

ID  technology  speed
1   5G  100

However, if the MAX(technology) ever has less than the MAX(speed), the above would become incorrect.

A better approach would be to use a window function because you would remove that potential flaw:

with cte as (
    SELECT ID, technology, speed,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY technology DESC) RN
    FROM table)
SELECT *
FROM cte
WHERE RN = 1

This assigns a number to each row, starting with number 1 for the row that has the MAX(technology) (ie. ORDER BY technology DESC), and does this for each ID (ie. PARTITION BY ID).

Therefore when we select only the rows that are assigned row number 1, we are getting the full row for each max technology / id combination.

One last note - if there are duplicate rows with the same ID and technology but with various speeds, this would pick one of them at random. You would need to further include an ORDER for speed in that case. Based on your sample set this doesn't happen, but just an fyi.

score 1 · Answer 2 · answered Mar 30 '22 at 18:53

You can also use the keep keyword. It is handy in situations like this one since ordering by one column and outputting another column happens in one clause, no subquery or CTE is needed. The drawback is that it is Oracle proprietary syntax.

with t (ID,     technology,     speed) as (
select 1,   '3G',   20  from dual union all
select 1,   '2G',   10  from dual union all
select 1,   '4G',   40  from dual union all
select 1,   '5G',   100 from dual union all
select 2,   '3G',   60  from dual union all
select 2,   '4G',   90  from dual union all
select 2,   '5G',   150 from dual union all
select 3,   '3G',   30  from dual union all
select 3,   '4G',   50  from dual
)
select id
     , max(technology) keep (dense_rank first order by speed desc)
     , max(speed)
from t
group by id

Db fiddle.

ORACLE SQL: select MAX value based on 2 different variables (group by function)

2 Answers2