problem with group by and maximum

[Alex Prentki]

Hi guys,
i have the following TABLE:

FIELD1 FIELD2 FIELD3
1 A 23
1 B 77 <<< i want to select this row
2 C 12
2 D 99 <<< and this one
2 E 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the
group grouped by FIELD1!

The following query works if i leave out FIELD2:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well without getting a
SQL error.

Any ideas?

Thanks in advance.
Alex

[Serge Rielau]

Alex Prentki wrote:

Hi guys,
i have the following TABLE:

FIELD1 FIELD2 FIELD3
1 A 23
1 B 77 <<< i want to select this row
2 C 12
2 D 99 <<< and this one
2 E 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the
group grouped by FIELD1!

The following query works if i leave out FIELD2:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well without getting a
SQL error.

Any ideas?

Thanks in advance.
Alex

Try this:
select field1, field2, field3
from (select
field1,
field2,
field3,
row_number() over (partition by field1
order by field3 desc)
as rn
from table) AS X
where rn = 1;

The problem with group by is that there is no such thing as:
"Give me the row that produced the max() for this partition)"

Cheers
Serge

[Knut Stolze]

Alex Prentki wrote:

Hi guys,
i have the following TABLE:

FIELD1 FIELD2 FIELD3
1 A 23
1 B 77 <<< i want to select this row
2 C 12
2 D 99 <<< and this one
2 E 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the
group grouped by FIELD1!

The following query works if i leave out FIELD2:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well without getting a
SQL error.

SELECT field1, field2, field3
FROM table
WHERE ( field1, field3 ) IN ( SELECT field1, MAX(field3)
FROM table
GROUP BY field1 )

--
Knut Stolze
Information Integration
IBM Germany / University of Jena