On 6/21/05, comex wrote:
> I have a table:
> create table example(time datetime, username varchar(255));
Please tell me you didn't actualy use "time" as identifier :)
> time username
> 2005-06-21 15:58:02 user1
> 2005-06-21 14:58:02 user1
> 2005-06-21 11:57:51 user2
> 2005-06-21 10:57:51 user1
> 2005-06-21 09:57:51 user1
> I want it, however, to return:
>
> COUNT(*) username maxtime
> 2 user1 2005-06-21 15:58:02
> 1 user2 2005-06-21 11:57:51
> 2 user1 2005-06-21 10:57:51
SELECT
username,
MAX(time),
COUNT(username)
FROM (
SELECT
username,
time,
(SELECT COUNT(e2.time)
FROM example e2
WHERE e2.time < e1.time AND e2.username <> e1.username) AS usersession
FROM
example e1) AS intermed
GROUP BY
username,
usersession
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
