Select Distinct only on certain columns?

We have a table with 10 columns, which log user actions.

Let’s say four of them are: time, visitor, visitor_info, siteid

sample data looks like this:
time, visitor, visitor_info, siteid
a)10, 1, 2, 5
b)10, 2, 7, 9
c)11, 1, 2, 5
d)12, 3, 8, 9
e)12, 1, 2, 5

as you can see rows A, C, E are identical except the time part. If I do a select distinct on it, all three rows are returned (as they should), but is it possible to do a select distinct ignoring a specific column, so it would only return a result of one row? (ie a distinct only on visitor, visitor_info, and siteid)?

This is for MySQL5 (using PHP5).

Thank you for any insight.

yes, it’s possible, but not with DISTINCT

select visitor
     , visitor_info
     , siteid
     , max(time) as max_time
  from daTable
group
    by visitor
     , visitor_info
     , siteid

or simply don’t include time in your query.

R937,
Thank you, this works wonderfuly, can I complicate the problem now?
Assuming the time is stored in UNIX seconds format, how can I limit this filtering to only repeated logs with in a 2 minute time frame?

Ie, if we refer back to our sample data set (pretend dtime is unix format)

time, visitor, visitor_info, siteid
a)10, 1, 2, 5
c)11, 1, 2, 5
e)15, 1, 2, 5

difference between a and c is one minute so that has to be filtered out, but the next user action e) is 4 minutes past the last one and thus is proly authenticly desired. Is there anyway we can extend that query to take time interval into account?

what you want to do is select those rows which do not have a matching row within the last two minutes, where “matching” means the same visitor, visitor_info, siteid

correct?

select visitor
     , visitor_info
     , siteid
  from daTable as T
 where not exists
       ( select *
           from daTable
          where visitor      = T.visitor
            and visitor_info = T.visitor_info
            and siteid       = T.siteid
            and time between T.time - 120
                         and T.time )