0

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:

enter image description here

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() . "'
Bradley
  • 132
  • 3
  • 15
  • 1
    `GROUP BY` doesn't return rows from the table, it computes [aggregate values](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) for groups of rows. `GROUP BY` cannot solve your problem. Take a look at Take a look at [this answer](https://stackoverflow.com/a/28090544/4265352) on a [similar question](https://stackoverflow.com/q/12102200/4265352). – axiac Nov 10 '17 at 06:50
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Nov 10 '17 at 07:40
  • @axiac Even back in 2015, that was an archaic method. These days we’d tend to use variables, or correlated subqueries, or uncorrelated subqueries. – Strawberry Nov 10 '17 at 07:44
  • @Strawberry using variables doesn't look like SQL to me. Subqueries are fine but it depends. A subquery that uses `GROUP BY` is worse than a `JOIN` and a subquery that doesn't use `GROUP BY` is converted into a `JOIN` by the query engine, if possible (at least this is what happens on MySQL). – axiac Nov 10 '17 at 07:52
  • @axiac Regardless of what’s going on under the hood, the alternatives are vastly more performant. I’m fond of the old method (in some circles it’s known as a ‘strawberry query’, after all), but it’s time to relegate it to history. – Strawberry Nov 10 '17 at 08:07

1 Answers1

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
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107