Query problem please help

[rcfearn]

Hi,

I have a shopping cart table that I want to clean up. The rule I want to use to cleanup the table is:

If the user added their last item (marked with *) to the cart more than 30 minutes ago delete everything for that user.

USERID TS

18 2006-07-05 20:57:12
18 2006-09-05 22:12:27
18 2006-09-05 22:45:45 +
18 2006-09-05 23:43:58 *
17 2006-09-05 22:44:22 +
17 2006-09-05 23:50:00 *

I have tried using the query

SELECT MAX(TS) as TS,USERID FROM cart WHERE DATE_SUB(NOW(),INTERVAL 30 MINUTES) > TS GROUP BY USERID;

The problem with this is that it returns the MAX(TS) older than 30 Minutes but not the absolute MAX(TS) for the userid.

For example if NOW() is 2006-09-05 23:50:45 it will return
18 2006-09-05 22:45:45 +
17 2006-09-05 22:44:22 +

When I actually want it to return an empty set.

Any help would be most appreciated.

Cheers
Rob

 

[KarveR]

Are the * and + actually in the same field as the timestamp ?


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

select userid 
  from cart 
group 
    by userid
having max(ts) < date_sub(now(),interval 30 minute)

this will give you a list of userids that can be deleted

r937.com | rudy.ca

 

[rcfearn]

Thanks r937, just tried it out, that's perfect!!

(Karver) The + && * were added at post time to make it clear about what I was asking, they were not part of timestamp ;-)