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
>implementation.)
>
>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,
>..chris


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

CREATE TEMPORARY TABLE tmp
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 = 
tmp.max_timestamp

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