选择Distinct / Unique但返回所有列

Question

I have a script setup to report a bunch of data periodically, such as IP address, and the page a visitor is on.

I need my backend script to display the latest page that an IP address has visited.

My database is setup like this:

I want to be able to use all of the columns, such as "browseid" and "page". I want to retrieve the last value only. To give you a frame of reference, using the example data above, I'd only want to retrieve record 60, because it's the last page that the IP xxx.xx.xxx.xx visited.

I've been trying to find a solution that works for the last hour, but I just can't get one to work. I'm also trying to select data within a certain timeframe. Here's what I've been using to check for that:

WHERE ts >= '" . strtotime("-15 minutes") . "' AND expiry <= '" . time() . "'

Answer 1

You could use a join on the max id groped by ip eg:

 select * from my_table m 
 inner join  (
    select clientip, max(browseid) as m_id 
    from my_table
    group by clientip
 ) t on t.clientip = m.clientip and t.m_id = m.browseid