Help! Group by description and return top id for each instance? 1

[Trebor100]

Hi,

Im stuck... really stuck.

The situation is this - i've got a table which has an id field and a description field along the lines of:

ID Desc
1 Asda
2 Tesco
3 Safeway
4 Asda
5 Tesco
6 Tesco

What i need to do is return one instance of each desc together with a corresponding id. For instance with the example shown above i would need:

ID Desc
1 Asda
2 Tesco
3 Safeway

any ideas as seriosuly stuck. please note that i cannot change the table structure to make it unique on description

 

[gmmastros]

Try...

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Min[/color](Id), Desc
[COLOR=blue]From[/color]   TableName
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Desc

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Trebor100]

cheers dude - i was going completly the wrong way with this one (trying to do it with joins etc)

thanks again.
Rob

 

[r937]

caution, DESC is a reserved word, i would always use descr as the column name

r937.com | rudy.ca