Hello.
On Mon, Jul 16, 2001 at 05:51:34PM -0700, [EMAIL PROTECTED] wrote:
> Question:
>
> given data in a table such as
>
> id, value, date
> 0, value1, 1999-01-25
> 1, value2, 1999-01-02
> 2, value2, 1999-01-04
> 3, value1, 1999-01-22
> 4, value2, 2000-01-01
>
> how can i select the latest 'id' for a given same set of 'value' based on
> 'date'? ie: i would like to be able to get records id's 0 and 4 from
> above, since id "0" has the latest date for value1 (1999-01-25) and id "4"
> has the least date for value2 (2000-01-01).
Assuming, that 'date' is really some kind of DATE type and not CHAR,
you may want to try the following:
SELECT *
FROM your_table
WHERE value='value1'
ORDER BY date
LIMIT 1
which will return the records with 'value1' in ascending order and
restrict the result to the first one, i.e. the one with the latest
date.
SELECT *
FROM your_table
WHERE value='value2'
ORDER BY date DESC
LIMIT 1
will do the same for 'value2' and the least date.
> i was thinking of using a least function on it, but my problem is I don't
> know how exactly it can be done without some (outside) processing. Am I
> missing something?
Regards,
Benjamin.
---------------------------------------------------------------------
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
