Selecting rows with the highest date

Question

I have some values which are repeating in my table, I want to select only those with the latest/highest date i.e :

ID   Type     Name      Value        Date
--  -------   -----    -------       -------------
1   "FRUIT"  "APPLE"  "Imported"  "2011-03-19 22:08:13"
5   "FRUIT"  "LEMON"  "Imported"  "2011-03-19 22:00:44"
22  "FRUIT"  "PEACH"  "Imported"  "2011-03-20 11:03:13"
31  "FRUIT"  "MELON"  "Imported"  "2011-04-28 18:42:07"
44  "FRUIT"  "PEACH"  "Imported"  "2011-04-12 11:06:11"
98  "FRUIT"  "CHERRY" "Imported"  "2011-03-19 22:46:04"
211 "FRUIT"  "MELON"  "Imported"  "2011-03-19 22:25:24"
217 "VEG"    "SPINACH""Imported"  "2011-03-19 22:25:24"

I'd like to select these :

ID   Type     Name      Value        Date
--  -------   -----    -------       -------------
1   "FRUIT"  "APPLE"  "Imported"  "2011-03-19 22:08:13"
5   "FRUIT"  "LEMON"  "Imported"  "2011-03-19 22:00:44"
31  "FRUIT"  "MELON"  "Imported"  "2011-04-28 18:42:07"
44  "FRUIT"  "PEACH"  "Imported"  "2011-04-12 11:06:11"
98  "FRUIT"  "CHERRY" "Imported"  "2011-03-19 22:46:04"

This is simplified version of what I need, my table has about 20 columns so I want select *, if not I can select one by one.

So I want to select * rows of Type FRUIT but select only those with highest date. Thank you

Answer 1

This should give you what you want:

SELECT * FROM Table INNER JOIN  (SELECT Name, MAX(Date) as TopDate FROM Table WHERE Type = 'FRUIT' GROUP BY Name) AS EachItem ON      EachItem.TopDate = Table.Date      AND EachItem.Name = Table.Name 

Basically, it will find the latest date for each type of fruit and then display each fruit with the information for the row (joined on the date and fruit name). Make sure the Date field and Name field are both indexed.

If you could assume that the item with the highest ID would also be the one with the highest date (typical but not necessarily true in all cases - it depends on your use case), you could do MAX(ID) instead of MAX(Date) and take advantage of just linking by that ID instead of linking by Date and Name.

Answer 2

If you are using mysql this will order the SELECT all the type "fruit" and will order the date from oldest to newest:

 SELECT * FROM tablename WHERE Type='fruit' ORDER by Date ASC 

You could alo put a limit on it if needed (This would limit to the oldest 5):

 SELECT * FROM tablename WHERE Type='fruit' ORDER by Date ASC LIMIT 5