simple select distinct query question

[kiblinger]

SQL newbie. Can't think how to search for this so I'll just ask in hopes that someone is patient enough to answer:


In a table called "products" I have 2 fields:

item_code menu_name
1 chairs
2 chairs
3 tables
4 tables
5 tables
6 desks
7 benches
8 benches



item_codes are all unique, while menu_names can repeat. I can SELECT DISTINCT from menu_name to get:

chairs
tables
desks
benches

but I also want each distinct menu_name's first item_code to come along with it so I get:

1, chairs
3, tables
6, desks
7, benches

Thanks all.

 

[gmmastros]

Select Min(Item_Code) As Item_Code,
       Menu_Name
From   Products
Group By Menu_Name

-George

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

 

[kiblinger]

Cool! Thanks!

Now, what if I have:

item_code menu_name flag
a1 chairs x
2ee chairs
xyz tables
4s tables x
5 tables
6r desks x
aa benches
ggg benches x

and I want:

a1, chairs, x
4s, tables, x
6r, desks, x
ggg, benches, x

Thanks!

 

[ca8msm]

Add a where clause looking for an "x" in the flag field


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[kiblinger]

Ah right - of course.

At that point I can just use:

SELECT * FROM products WHERE flag = 'x'

to get what I am ultimately going to need....

Thanks again.