Another MySQL query

The long week is catching up with me and my mind is blank as to what the query is I'm looking for.

I've got a table of fruit that I keep getting orders for;

table: fruit

id fruit name stock sellby date


Every time a new supply of fruit comes in it gets added to the database so an example state of the database might be;


id fruit name stock sellby date

1 apple 5 11/04/06
2 orange 7 09/04/06
3 banana 3 09/04/06
4 apple 12 15/04/06



What I want to do is basically select all the most recent orders for each fruit. Would it be SELECT id, DISTINCT fruit_name, stock, sellbydate FROM fruit ORDER BY id DESC ??

I was thinking it might be that but I thought the DISTINCT operator might mess things up.

 

Shawn if you see this thread can you move it to the databases forum. I forgot that it had a section of it's own now.

 

I think you need to setup another table for recent_orders. The table would have columns with id, fruit name, date_col. Then use something like:

SELECT * FROM recent_order WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

 

In the end I did almost exactly what you said. I made a column called newest and when the table updates an order it sets the new column to 1 and the old columns back to 0.

I just had it in my mind that I could do it with a query but of course this way is a lot less stressful on the server

 

Cool, glad you managed it.

 

Thanks for your help buddy