site version date ---- --------- ------- A jkjk 8 Jan 2003 A Unavailable 10 Jan 2003 B kjsd 8 Jan 2003 B kjsd 10 Jan 2003 B kjsd 10 Jan 2003 C yuyu 10 Jan 2003
Unfortunately, the versions are not in numeric or alphabetical order so I have to determine which is the latest by looking at the date they were identified on. I want a report that will group the sites and give me the latest version for each. Note that site A\'s version should be jkjk with a datestamp of 8th Jan where the other sites should have a datestamp of the 10th Jan.
Normally I\'d do it using a nested SELECT query but as I can\'t do it in MySQL, I\'m a bit stuck!
Something along the lines of :
SELECT site, version, Max(date) FROM tableA WHERE version != \'Unavailable\' GROUP BY site ORDER BY site
gets me close ish but completely omits the site if it identifies the max(date) as having a version of \'unavailable\' (so in this example, site A would not appear)
Hope that makes sense and hope someone can help.