Re: [PHP-DB] Is the query I need even possible?

2003-03-19 Thread Bob Hall 
On Wed, Mar 19, 2003 at 07:37:46PM -0500, Mike Mannakee wrote:
> I have a table that looks like this:
> 
> page_idvisitor_idtimestamp
> ...
> 13   20030101060522
> 58   20030101060512
> 26   20030101060634
> 73   20030101060651
> 35   20030101060612  --> Earliest
> record with this visitor id
> 85   20030101060738
> 21  5   20030101060759
> 15   20030101060810
> 75   20030101060827 --> Last record
> with this visitor id
> 510 20030101060944
> 16   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

[PHP-DB] Is the query I need even possible?

2003-03-19 Thread Mike Mannakee 
I have a table that looks like this:

page_idvisitor_idtimestamp
...
13   20030101060522
58   20030101060512
26   20030101060634
73   20030101060651
35   20030101060612  --> Earliest
record with this visitor id
85   20030101060738
21  5   20030101060759
15   20030101060810
75   20030101060827 --> Last record
with this visitor id
510 20030101060944
16   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.

Currently I'm getting it like this:

SELECT DISTINCT(visitor_id) FROM (table)

Then going through each row with:

SELECT page_id FROM (table) WHERE visitor_id = '$visitor_id'  ORDER BY
date_time ASC LIMIT 1

to get the first and then:

SELECT page_id FROM (table) WHERE visitor_id = '$visitor_id'  ORDER BY
date_time DESC LIMIT 1

to get the last.

There must be an easier way I'm not seeing.  This, on the scale of thousands
of visitors, takes forever.  Is there any other way to do it?

TIA,

Mike





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php