Group By problem

Hello,

I have a problem of grouping data in a table



Here's my table

mysql> select * from url_histo;
+----+---------------------+------------+--------+
| id | timestamp | retourhttp | id_url |
+----+---------------------+------------+--------+
| 7 | 2006-05-04 12:37:48 | 200 | 1 |
| 8 | 2006-05-04 12:37:50 | 200 | 1 |
| 9 | 2006-05-04 14:47:47 | 200 | 2 |
| 10 | 2006-05-04 14:47:56 | 201 | 2 |
| 11 | 2006-05-04 14:48:01 | 201 | 1 |
+----+---------------------+------------+--------+

I want to have a query that return

| 10 | 2006-05-04 14:47:56 | 201 | 2 |
| 11 | 2006-05-04 14:48:01 | 201 | 1 |


This query should return the last (temporarly speaking) data of each
id_url. There is a field timestamp, it should be easy but I am
completely lost with "Group by"

mysql> select id,id_url,retourhttp,max(timestamp) from url_histo group
by id_url;

+----+--------+------------+---------------------+
| id | id_url | retourhttp | max(timestamp) |
+----+--------+------------+---------------------+
| 7 | 1 | 200 | 2006-05-04 14:48:01 |
| 9 | 2 | 200 | 2006-05-04 14:47:56 |
+----+--------+------------+---------------------+


The result is a mix of lines... I do not understand the way to do it



If anyone has a clue... thanks in advance

Dim wrote:
> Here's my table
>
> mysql> select * from url_histo;
> +----+---------------------+------------+--------+
> | id | timestamp | retourhttp | id_url |
> +----+---------------------+------------+--------+
> | 7 | 2006-05-04 12:37:48 | 200 | 1 |
> | 8 | 2006-05-04 12:37:50 | 200 | 1 |
> | 9 | 2006-05-04 14:47:47 | 200 | 2 |
> | 10 | 2006-05-04 14:47:56 | 201 | 2 |
> | 11 | 2006-05-04 14:48:01 | 201 | 1 |
> +----+---------------------+------------+--------+
>
> I want to have a query that return
>
> | 10 | 2006-05-04 14:47:56 | 201 | 2 |
> | 11 | 2006-05-04 14:48:01 | 201 | 1 |
>
>
> This query should return the last (temporarly speaking) data of each
> id_url. There is a field timestamp, it should be easy but I am
> completely lost with "Group by"
>
> mysql> select id,id_url,retourhttp,max(timestamp) from url_histo group
> by id_url;
>
> +----+--------+------------+---------------------+
> | id | id_url | retourhttp | max(timestamp) |
> +----+--------+------------+---------------------+
> | 7 | 1 | 200 | 2006-05-04 14:48:01 |
> | 9 | 2 | 200 | 2006-05-04 14:47:56 |
> +----+--------+------------+---------------------+

This gives you the maximal timestamp for each id_url. All other fields
(those you do not mention in the GROUP BY) give you an arbitrary result.

> The result is a mix of lines... I do not understand the way to do it

You are looking for the rows holding the group-wise maximum, which is a
separate Chapter in the fine manual:

http://dev.mysql.com/doc/refman/5.0/...group-row.html

Greetings
Kai

--
This signature is left as an exercise for the reader.

So...

SELECT *
FROM url_histo u1
WHERE timestamp = (
SELECT MAX( timestamp )
FROM url_histo
WHERE u1.id_url = id_url )