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
