Re: GROUP BY with MAX

[Egor Egorov]

Batara Kesuma <bkesuma@stripped> 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
 / /|_/ / // /\ \/ /_/ / /__   Egor.Egorov@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com