group by colour and code, then select lowest stock quantity

[stevebanks]

Hi,

Got another problem, some of our products come in 3 /4 boxes, and we have different stock levels for each box. I need to get the lowest number in stock for a product as a whole, per colour.

at the moment i've built a query which produces:
code | colour | box number | currentstock | backorderedstock | deliverydate |

e.g.

1010 | BLUE | 1 | 20 | 0 | 01/01/07
1010 | BLUE | 2 | 15 | 0 | 01/01/07
1010 | BLUE | 3 | 11 | 0 | 01/01/07

So the max that can be sold is 11... How can i get this to
look like:
1010 | BLUE | 11 | 0 | 01/01/07
1010 | RED | 14 | 0 | 01/01/07

Thanks very much!

 

[jrbarnett]

select code, colour, min (currentstock),backordererdstock, deliverydate
from YourTableName
group by code, colour, backordererdstock, deliverydate

John

 

[stevebanks]

Thank you! Simple when you know how!!!