Re: SELECT earliest unique records

[Chris]

The answer depends on what version of MySQL you're using. If it's prior 
to 4.1 then there is no good way (if any at all) to do it in a single query.

Prior to 4.1:
CREATE TEMPORARY TABLE tmptable SELECT item_id,MAX(date_field) as 
date_field FROM table GROUP BY item_id;

SELECT tblone.* FROM table tblone INNER JOIN tmptable tbltwo 
ON(tblone.item_id=tbltwo.item_id);

DROP TABLE tmptable;
4.1+:
SELECT * FROM table tblone WHERE date_field = (SELECT 
MAX(tbltwo.date_field) FROM table tbltwo WHERE 
tbltwo.item_id=tblone.item_id);

Note: these are examples only, I'd be surprised if they did exactly what 
you wanted, but they are close and convey the general gist of what you 
need to do.

Chris
Dan Sashko wrote:
thank you, this does return the proper date field for the item_id, 
however the rest of the fields of the records are still from the first 
record in the table.

How would you make it so that the entire row is the one that contains 
the earliest data field ?

----- Original Message ----- From: "Jay Blanchard" 
<[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 12, 2004 2:47 PM
Subject: RE: SELECT earliest unique records

[snip]
I have a table that contains a item_id field (non unique index) id field
and
a date field.
How would you go about selecting rows from the table (single row for
each
item_id with the earliest date field for that item_id).
If I use group by item_id the date field will be whatever the first date
field it had, so ordering by it at that point will not yeald the
intended
results.
[/snip]
Use MAX(date field)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]