HELP WITH GROUP BY

[drforbin1970]

This should be easy but can't seem to figure it out today.

Have a table with three columns and corresponding data:
ID / prod_desc / flag_id

100 / JB / 0
100 / JB / 1
100 / JB / 2
101 / ME / 3
102 / QU / 4

I need the output to read:
100 / JB / <any JB record, can be 0, 1 or 2>
101 / ME / 3
102 / QU / 4

Can't GROUP BY because of flag_id column. Tried DISTINCT and self-join. Can't
figure out.

Help is appreciated, thanks in advance!

[philh]

Originally posted by: drforbin1970


I need the output to read:
100 / JB / <any JB record, can be 0, 1 or 2>
101 / ME / 3
102 / QU / 4




Do you need ONLY the ME/ 3 and QU/4 records?

SELECT TOP 1 * FROM Mytable WHERE prod_desc = 'JB'
UNION
SELECT * from Mytable where (Prod_desc = 'ME' and flag_id = 3) or (prod_desc
= 'QU' and flag_id = 4)

[drforbin1970]

Those values are not known beforehand, need to group the data somehow.

[Dan Bracuk]

If you don't need the flagid, don't select it. Also use the keyword distinct in your select clause. You won't have to group.

[healey_mark]

Alternatively, you could use MAX or MIN aggregate functions for this:

select MIN( ID ), prod_desc, MIN(flag_id)
from theTable
group by prod_desc