Batara Kesuma <[EMAIL PROTECTED]> wrote:
> I have a table that looks like:
>
> mysql> select * from test3;
> +--------+------------+------------+
> | sub_id | date | data |
> +--------+------------+------------+
> | 1 | 2004-05-01 | data 001 |
> | 1 | 2004-05-02 | data 002 |
> | 1 | 2004-05-03 | data 003 |
> | 2 | 2004-06-01 | data 2 001 |
> | 2 | 2004-06-02 | data 2 002 |
> | 2 | 2004-06-03 | data 2 003 |
> +--------+------------+------------+
> 6 rows in set (0.00 sec)
>
>
> If I group it by sub_id this is what I get:
>
> mysql> select * from test3 group by sub_id;
> +--------+------------+------------+
> | sub_id | date | data |
> +--------+------------+------------+
> | 1 | 2004-05-01 | data 001 |
> | 2 | 2004-06-01 | data 2 001 |
> +--------+------------+------------+
> 2 rows in set (0.01 sec)
>
> I want to get the data from the MAX(date) grouped by sub_id, the result
> I want is:
>
> +--------+------------+------------+
> | sub_id | date | data |
> +--------+------------+------------+
> | 1 | 2004-05-03 | data 003 |
> | 2 | 2004-06-03 | data 2 003 |
> +--------+------------+------------+
>
>
> How can I do that?
>
CREATE TEMPORARY TABLE tmp SELECT sub_id, MAX(date) AS date FROM test3 GROUP BY sub_id;
SELECT test3.* FROM test3, tmp WHERE test3.sub_id=tmp.sub_id AND test3.date=tmp.date;
If your version of MySQL server >=4.1, subqueries may help you.
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
