grouping question

pn idc cnt
------- ---- ----
a7447 1 3
a7447 2 4
a7447 3 5
a7447 7 6
a7447 8 7
a7447 9 9
a7447 11 1
a7447 12 2
a7447 15 8
a7474 5 1
a7474 6 2
a7474 13 4
a7474 14 5
a7474 16 3

from this data i need to group by pn field and get the idc for max cnt
cnt could be repeated and for those possibilities it doesnt matter get multiple or just one of them doesnt matter.
i hope this make sense

the result for this list should look like

pn idc cnt
------- ---- ----
a7447 9 9
a7474 14 5select pn
, idc
, cnt
from daTable as T
where cnt =
( select max(cnt)
from daTable
where pn = T.pn )|||Hi
It should work as you described.

select t1.*
from TheTable t1 inner join (select pn,
cnt = max(cnt)
from TheTable
group by pn) as t2 on t1.pn = t2.pn and
t1.cnt = t2.cnt
order by t1.pn,
t1.idc

Regards
Kris Zywczyk|||Thats what i did for this occation it does work but if i have multiple on the same cnt and if i need only one of them then we have a problem. But ill deal with that later on.
select pn
, idc
, cnt
from daTable as T
where cnt =
( select max(cnt)
from daTable
where pn = T.pn )