[EMAIL PROTECTED] wrote: > > Here is a FABRICATED table I have TYPED OUT to demonstrate the information > that I'm looking for. > I'm looking for a single query that can produce: > > +----------+----------+--------------------+-----------------------+ > | clientid | revision | name | address | > +----------+----------+--------------------+-----------------------+ > | 1 | 1 | Fred Flintstone | 145 Stonewall Terrace | > | 2 | 0 | Barney Rubble | 57 Bedrock Way | > | 3 | 2 | Pebbles Flintstone | 25 Mammoth Road | > | 4 | 1 | Bam Bam Rubble | 25 Mammoth Road | > +----------+----------+--------------------+-----------------------+ > > if it were legal and it worked, I could do a > > SELECT clientid, revision, name, address FROM foobar WHERE revision = > max(revision) GROUP BY clientid; > > I dunno - I'm thinking I have to do something with the indexes that I'm > missing. > > Any help would be appreciated.
Just thinking through this... One possibility would be to run multiple selects. You could do the "SELECT max(revision), clientid FROM foobar GROUP BY clientid" first, then programmatically either cycle through all of them doing mini-selects or glop together a giant or statement (probably that will fail when you have large numbers of clients). You could create a temporary table, use an INSERT ... SELECT statement to populate it with the above data, then join that table with your original table. You could programmatically maintain a 'most recent' flag which would be update whenever a new record was inserted. I'm going to set up a fake DB like yours and experiment with it. ... ... Here we go, this seems to work: SELECT t1.clientid, max(t1.revision) as maxrev, t2.revision, t2.name, t2.address FROM foobar t1, foobar t2 WHERE t1.clientid=t2.clientid GROUP BY clientid, revision HAVING maxrev=revision; See if that helps any. -- John Klein, Database Applications Developer | Omnia Mutantur, Systems Group - Harvard Law School | Nihil Interit --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
