group by, max and another column
-
Hi, Say I have the table below: Tabl1 PKey ColA ColB ColC 1 a1 b1 c1 2 a1 b2 c2 3 a1 b3 c3 ... a2 ... ... ... a3 ... ... If I group by ColA with the select statement as follows:
SELECT ColA, MAX(ColB), FROM Tabl1 GROUP BY ColAIf for example for a1 the MAX(ColB) is b2, THEN I would like to include ColC in the select statement such that for a1 the resulting row would be: ColA ColB ColC a1 b2 c2 What is the best way to do this? TIA!I am a SysAdmin, I battle my own daemons.
-
Hi, Say I have the table below: Tabl1 PKey ColA ColB ColC 1 a1 b1 c1 2 a1 b2 c2 3 a1 b3 c3 ... a2 ... ... ... a3 ... ... If I group by ColA with the select statement as follows:
SELECT ColA, MAX(ColB), FROM Tabl1 GROUP BY ColAIf for example for a1 the MAX(ColB) is b2, THEN I would like to include ColC in the select statement such that for a1 the resulting row would be: ColA ColB ColC a1 b2 c2 What is the best way to do this? TIA!I am a SysAdmin, I battle my own daemons.
select b.* from ( select ColA, Max(ColB) as MaxColB from Tabl1 group by ColA ) as a Tabl1 as b on b.ColA = a.ColA and b.ColB = a.ColB order by b.ColA