[thelist] Sql query help

[Luther, Ron]

Robert Hanson noted that:


>>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...

... and then offered up a tricky sequence of steps to solve this all in SQL.


Hi Robert || Sarah,


First of all, I think I agree with Robert ... I'm not quite sure you've 
presented exactly what you are looking for.  (The reason I say that is 
because if your 'stats table' contains ... oh, let's say 6 months of data, 
then you could easily have 20 records in there meeting this criteria for 
one user and only 1 meeting this criteria for another user - even if each 
had 100 records in the table to begin with.) Now, if that's really what 
you want as output then I think I would write a simple SQL statement to 
sort the data by timestamp and put that recordset through a loop in some 
programming language to do the heavy lifting and timestamp comparison. 
Write the criterion matching records out to a second table and do a 
simple 'select and display' on the simplified table.

So let's talk about it on a 'go forward' basis instead ... allow me to 
change your application design (generous eh?) and add a new table called 
"four_hour_stats".  Now, when a client hits your application (1) log that 
hit in your normal 'stats' table, and (2) compare the current time to 
the 'most recent prior access' [this part involves SQL with a MAX(fld) 
in it] ... if the time differential is greater than 4 hours, then log 
this as a record in your shiny new "four_hour_stats" table. Lather, rinse, 
repeat.  That will give you a 'go forward' table built *only* with records 
from clients with a greater than 4 hour delta in access times.


HTH,

RonL.