Jump to content

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


tilda2000

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.