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