in reply to Re: Re: MySQL question
in thread MySQL question

Ahh. Then again, with a real database with subselects, that'd be: 
SELECT title, doc_num, version
FROM atable
WHERE  (doc_num,  version) IN (
  SELECT doc_num, MAX(version) FROM atable GROUP BY doc_num
);

[download]
Again, this won't work in puny MySQL. PostgreSQL or better, baby!

-- Randal L. Schwartz, Perl hacker

Replies are listed 'Best First'.
Re: •Re: Re: Re: MySQL question
by rbc (Curate) on Mar 29, 2002 at 17:10 UTC
    If Kozz cannot upgrade to PostgreSQL for some reason couldn't Kozz ape a subselect with by creating a tempory table "ttable" and then join "atable" with that? sonething like this ... 
    create table ttable as  
   SELECT doc_num, MAX(version) FROM atable GROUP BY doc_num;

SELECT title, doc_num, version
  FROM atable a, ttable t
  WHERE  a.doc_num = t.doc_num and a.version = t.version;

    [download]

    ... I don't know about the other monks I find SQL
    fun like its a weird puzzle.
[reply]
[d/l]

In Section Seekers of Perl Wisdom