I have a table something like :
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.
Thanks!