How can I avoid nested selects to do this...?

[tilda2000]

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!

[pallevillesen]

1. create temporary table TEMPT as select * from... where blabl <> \"Unavailable\";

 

 create temporary table TEMPT as

SELECT * FROM tableA

WHERE version != \'Unavailable";

 

2. Do your search on the TEMPT instead...

SELECT site, version, Max(date)

FROM TEMPT 

GROUP BY site

ORDER BY site

 

3. Drop TEMPT.

 

Basically create the table you want with the \"Unavailable\" rows excluded... then use this table for searching...

 

P.