[thelist] Sql query help

[Robert Hanson]

Sarah Sweeney writes:

My question is, how do I select only those records from my stats table
that have a timestamp of at least 4 hours more than the previous record
for the client. I'm thinking something along these lines...

SELECT    COUNT(spcurr.UserID) AS UserCount, spcurr.Country
FROM      Stats_Pages spcurr, Stats_Pages spprev
WHERE     spcurr.UserID = spprev.UserID
AND       MAX(spprev.Timestamp) < DATE_SUB(spcurr.Timestamp, INTERVAL 4
HOUR)
GROUP BY  sp.Country
ORDER BY  UserCount DESC

...but I know that the MAX() in the WHERE statement is not quite right.
Any ideas on how this can be accomplished?

------------

First, there are some great lists for questions like this;  check out
sqladvice.com


This is a start for you; but I think it does not produce what you want
(because your original query is not what you want).  But it illustrates a
process.

You could build this up in stages:

1) -- produces, for each record in the table, a set of records with a
timestamp less than the one in the record
-- this is probably NOT what you want.  You probably want a set of records
with a timestamp less than the max timestamp for a user.

select spcurr.UserId as userId, spcurr.County as county,
spprev.TimeStamp as prevTimeStamp, spcurr.TimeStamp as currTimeStamp
from stats_pages spcurr, stats_pages spprev
where spcurr.UserId = spprev.user_id
and spprev.TimeStamp < date_sub(......)

2)
select userId, county, max(prevTimestamp), timeStamp from
(select spcurr.UserId as userId, spcurr.County as county,
spprev.TimeStamp as prevTimeStamp, spcurr.TimeStamp as currTimeStamp
from stats_pages spcurr, stats_pages spprev
where spcurr.UserId = spprev.user_id
and spprev.TimeStamp < date_sub(......)
) x
group by user_id, timeStamp

3)

select count(*), county
from
( select userId, county, max(prevTimestamp), timeStamp from
(select spcurr.UserId as userId, spcurr.County as county,
spprev.TimeStamp as prevTimeStamp, spcurr.TimeStamp as currTimeStamp
from stats_pages spcurr, stats_pages spprev
where spcurr.UserId = spprev.user_id
and spprev.TimeStamp < date_sub(......)
) x
group by user_id, timeStamp
) y
group by county
order by ...