comex <[EMAIL PROTECTED]> wrote on 06/21/2005 11:07:35 AM: > I have a table: > create table example(time datetime, username varchar(255));
> 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 > The query: > select COUNT(*), username, MAX(time) as maxtime from example group by > username order by maxtime desc; > groups by username, and returns: > COUNT(*) username maxtime > 4 user1 2005-06-21 15:58:02 > 1 user2 2005-06-21 11:57:51 > 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 > That is, do not group entries by the same user together if another > user visited in between. Is this possible? It would be if possible you kept track of each connection with something like a Session_ID. Then the first connection of User1 would be with session 13. The connection by User2 would be session 14, and the reconnect of User1 could be on session 15. Basically it boils down to the fact that with SQL you have to use some other way of telling each group apart other than position (or interposition, as you say in your example). The fact that you have entries in your table from user1, user1, user2, user1, user2, user3, and user1 doesn't necessarily mean that user1 logged off each time so that user2 or user3 could login, does it? If that's actually the case, assign a unique tracking number to each login event and group your activities by that. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
