List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 27 2001 4:27pm
Subject:Re: max from a group?
View as plain text  
At 11:03 AM -0500 9/27/01, Chris Johnson wrote:
>Is it possible to do the following using the SQL language constructs
>available in MySQL?  (I know how to do it with Sybase, but Sybase's SQL
>extensions make it easy and they don't exist directly in MySQL's
>I have a table like this:
>| Field     | Type     | Null | Key | Default | Extra |
>| claimid   | int(11)  |      | PRI | 0       |       |
>| statcode  | char(2)  |      | PRI |         |       |
>| timestamp | datetime | YES  |     | NULL    |       |
>Here's some data to populate it as an example:
>INSERT INTO foo VALUES (1,'JJ','2001-08-01'), (1,'AA','2001-09-27'),
>    (2,'ZZ','2001-08-18'), (2,'AA','2001-09-27');
>What I want to do is select only the row which has the maximum timestamp for
>each group of claimid's.  In other words, for the sample data above, I want
>to return only 2 rows, one with the value 1, AA, 2001-09-27 and another with
>2, AA, 2001-09-27.
>This is sort of a "GROUP BY" kind of thing, but I need to get the
>max(timestamp) and the corresponding statcode for each group.  I'm
>embarrassed to admit I haven't been able to figure out how to do it.
>Any suggestions?
>Thanks much,

Create a temporary table that contains the maximum for each claimid:

SELECT claimid, MAX(timestamp) AS max_timestamp FROM claim_tbl GROUP BY claimid

Then join it with the original table the rows that match each maximum:

SELECT claim_tbl.* FROM claim_tbl, tmp
WHERE claim_tbl.claimid = tmp.claimid AND claim_tbl.timestamp = 

Paul DuBois, paul@stripped
max from a group?Chris Johnson27 Sep
  • Re: max from a group?Paul DuBois27 Sep
  • Re: max from a group?Harald Fuchs29 Sep