0

I have a JS sending back a log to a PHP page to insert an entry to a MYSQL DB in the following format:

id      ipad       uid  ts                  urlpath  
1455    1.1.1.15    13  19/01/2019 07:32    http://1.1.1.151/useradmin.php  
1456    1.1.1.15    13  19/01/2019 07:33    http://1.1.1.151/useradmin.php  
1460    1.1.1.15    13  19/01/2019 07:37    http://1.1.1.151/useradmin.php  
1461    1.1.1.15    19  19/01/2019 08:05    http://1.1.1.151/index.php  
1462    1.1.1.15    19  19/01/2019 08:05    http://1.1.1.151/index.php  
1463    1.1.1.15    19  19/01/2019 08:05    http://1.1.1.151/index.php  
1464    1.1.1.15    13  19/01/2019 23:13    http://1.1.1.151/useradmin.php

What I am trying to do is return the latest SINGLE entry for a uid by timestamp(ts) for a given period (e.g. 30 minutes, 6 hours, 30 days etc) I have tried several pieces of code but it never returns the latest entry. The code I am using at the moment is: 

SELECT *  
  FROM usertrack  
 WHERE ts >= NOW() - INTERVAL 43830 MINUTE  
 GROUP 
    BY uid  
HAVING MAX(ts)  
 ORDER 
    BY ts DESC

But this just picks a random entry and not the latest.

Any help would be greatly appreciated, as I've lost a large amount of hair already.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Keith
  • 3
  • 1
  • Do you want to group the results by uid or do you want to get a single entry for one uid? – Laurens Jan 20 '19 at 00:34
  • 1
    This sounds like a [tag:greatest-n-per-group] problem. Review other answers with that tag. It's likely that you're asking a type of question that has been answered many times. – Bill Karwin Jan 20 '19 at 00:38

1 Answers1

0

This could be done with a correlated subquery, like :

SELECT u.*
FROM usertrack u
WHERE NOT EXISTS (
    SELECT 1 FROM usertrack u1 WHERE u1.ts > u.ts AND u1.uid = u.uid
) AND ts >= NOW() - INTERVAL 43830 MINUTE
GMB
  • 216,147
  • 25
  • 84
  • 135