Jump to content


Photo

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


  • Please log in to reply
1 reply to this topic

#1 tilda2000

tilda2000
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 21 January 2003 - 03:08 PM

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!

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 23 January 2003 - 05:40 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users