select max from a group by

Question

I've got a very simple question, I hope.

I have a table MYTABLE like this:

FIELD1   FIELD2   FREQ
A1       B        10
A1       C        20
A1       D        5
A2       X        7
A2       Y        12
...

and I want to get something like this:

enter code here

FIELD1   FIELD2  
A1       C             
A2       Y

that is I want to get for every distinct FIELD1 the row having the max(FREQ) but also with its FIELD2 value

Oracle 10g

Thanks in advance!

Mark

What would be the expected result if also A1 - B had FREQ 20? — jarlh, Oct 15 '20 at 08:10

score 0 · Answer 1 · answered Oct 15 '20 at 08:19

you can use analytical functions to get this done

select * from (
select x.*,row_number() over(partition by field1,field2 order by field1 asc) as rnk from (
    select field1
           ,field2
           ,max(field2) over(partition by field1) as max_value
      from mytable
     )x
where max_value=field2
)y
where y.rnk=1

score 0 · Answer 2 · answered Oct 15 '20 at 08:20

0

Use window functions.

select FIELD1, FIELD2
from (
   select *, rank() over (partition by FIELD1 order by FREQ desc) rn
   from mytable
) t
where t.rn = 1

If you use RANK() then you return all rows with MAX(FREQ) for each FIELD1.

answered Oct 15 '20 at 08:20

Radim Bača

10,646
1
19
33

score 0 · Answer 3 · answered Oct 15 '20 at 08:20

0

use row_number()

select b.* from
(select a.* ,row_number()over (partition by FIELD1 orer by FREQ desc) rn
from table_name a
) b where b.rn=1

answered Oct 15 '20 at 08:20

Zaynul Abadin Tuhin

31,407
5
33
63

select max from a group by

3 Answers3