On Wed, Mar 19, 2003 at 07:37:46PM -0500, Mike Mannakee wrote: > I have a table that looks like this: > > page_id visitor_id timestamp > ... > 1 3 20030101060522 > 5 8 20030101060512 > 2 6 20030101060634 > 7 3 20030101060651 > 3 5 20030101060612 --> Earliest > record with this visitor id > 8 5 20030101060738 > 21 5 20030101060759 > 1 5 20030101060810 > 7 5 20030101060827 --> Last record > with this visitor id > 5 10 20030101060944 > 1 6 20030101061002 > ... etc > > > What I want to select is the PAGE_ID of the EARLIEST record and the PAGE_ID > of the LAST record, for each distinct visitor_id. I cannot figure out how > to do this without multiple, very slow sql calls. This is pure vi code. (Runs fine in vi; haven't tried it in MySQL.) If the combination of page_id and visitor_id is a key, then this should work. If page_id and visitor_id are not a key (unique identifier), then use the table's primary key instead. If you don't have a primary key column, consider adding an autonumber primary key and using that. The point is that the column(s) in the GROUP BY clause have to uniquely identify the row in the first table. SELECT v1.page_id, v1.visitor_id, v1.timestamp FROM visit v1, visit v2 WHERE vi.visitor_id = v2.visitor_id GROUP BY v1.page_id, v1.visitor_id HAVING Max(v1.timestamp) = Max(v2.timestamp); Substitute Min() for Max() to get the earliest visit. Bob Hall -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
