here's the scoop: Basically I'm listing pieces of information from a two-key table and I am not getting the information I want.
BACKGROUND Consider this sample table (which although goofy, does illustrate what I'm attempting to do) SELECT clientid, revision, name, address FROM clients; +----------+----------+--------------------+-----------------------+ | clientid | revision | name | address | +----------+----------+--------------------+-----------------------+ | 1 | 0 | Fred Flintstone | 55 Bedrock Way | <- Fred | 2 | 0 | Barney Rubble | 57 Bedrock Way | <- Barney | 3 | 0 | Pebbles Flintstone | 55 Bedrock Way | <- Pebbles lives with Fred | 4 | 0 | Bam Bam Rubble | 57 Bedrock Way | <- Bam Bam lives with Barney | 1 | 1 | Fred Flintstone | 145 Stonewall Terrace | <- Fred Moves | 3 | 1 | Pebbles Flintstone | 145 Stonewall Terrace | <- Pebbles Moves with him | 4 | 1 | Bam Bam Rubble | 25 Mammoth Road | <- Bam-Bam Moves out | 3 | 2 | Pebbles Rubble | 25 Mammoth Road | <- Pebbles changes name; moves in with BamBam +----------+----------+--------------------+-----------------------+ By using the clientid and revision keys, you can create a 'snapshot' of client data. For example, if you made an invoice out to Fred, when his address was 55 Bedrock Way, the invoices table, would record clientid and revision id. That invoice record will always pull up the contact information Fred had at the time the invoice was printed. PROBLEM I want to construct a single query that will list the latest record for each unique person. In the above query, we are listing all of the clientids, and all of the revisions. If we group by client, we get: +----------+----------+--------------------+----------------+ | clientid | revision | name | address | +----------+----------+--------------------+----------------+ | 1 | 0 | Fred Flintstone | 55 Bedrock Way | | 2 | 0 | Barney Rubble | 57 Bedrock Way | | 3 | 0 | Pebbles Flintstone | 55 Bedrock Way | | 4 | 0 | Bam Bam Rubble | 57 Bedrock Way | +----------+----------+--------------------+----------------+ This provides me with one piece of information I require, which is the unique number of clients, but the revision listed is the lowest, and I want the highest. SELECT clientid, max(revision), name, address FROM clients GROUP BY clientid; +----------+---------------+--------------------+----------------+ | clientid | max(revision) | name | address | +----------+---------------+--------------------+----------------+ | 1 | 1 | Fred Flintstone | 55 Bedrock Way | | 2 | 0 | Barney Rubble | 57 Bedrock Way | | 3 | 2 | Pebbles Flintstone | 55 Bedrock Way | | 4 | 1 | Bam Bam Rubble | 57 Bedrock Way | +----------+---------------+--------------------+----------------+ This gives me the highest revision number as well, but the name and address do not correspond to the clientid and revision listed. 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. --------------------------------------------------------------------- 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
