Retrieve latest time row using Group by not working

Question

Tables :-

Table 1 :- Person_LatLong

Person_Id
Lat
Lng
Location_DateTime

Person_LatLong Data :-

10  23.030211421184454  72.55705489668806   2014-09-02 04:23:42
10  23.03029215373424   72.55710855670746   2014-09-02 04:25:09
10  23.030301667271765  72.55715279051745   2014-09-02 04:27:21
11  19.045563510415214  72.915665750359 2014-09-02 08:22:07
11  19.046567530190785  72.91524330185979   2014-09-02 08:32:36
11  19.04553070382594   72.91621148133476   2014-09-02 08:39:47
11  18.537267778519347  73.83538450865574   2014-09-03 01:44:19
11  18.33554237666039   73.85274219500492   2014-09-03 07:18:02
11  18.331919816746026  73.8525499279805    2014-09-03 07:18:59
11  18.33181875247372   73.85243149060277   2014-09-03 07:19:02
11  18.777939290860722  73.31834934181029   2014-09-04 22:07:31
11  18.790032969638293  73.30265963437363   2014-09-04 22:09:06
11  18.79108238318203   73.29928216416553   2014-09-04 22:09:22
11  18.800857529132163  73.28531940244517   2014-09-04 22:11:22
11  18.812675453346255  73.27794458217039   2014-09-04 22:13:26
11  18.82985965773455   73.25592224937081   2014-09-04 22:15:31
11  18.84531169311457   73.23344887176076   2014-09-04 22:17:49
11  18.869063931831764  73.2185512231118    2014-09-04 22:19:54
11  18.893204517796047  73.20479873759578   2014-09-04 22:22:03
11  18.910161939581506  73.18348844819505   2014-09-04 22:24:30

Table 2:- LS_For

Subject_Id -> This is basically Person_Id but I have changed the name.
Watcher_Id
Assistance_Group_Id

LS_For Table Data :-

1   10  1
1   11  1
1   17  1

Query:-

SELECT *,( 6371 * acos( cos( radians(23.030) ) 
* cos( radians(Lat) ) * cos( radians(Lng) - radians(72.5570) )
 + sin( radians(23.030) ) * sin( radians(Lat) ) ) ) 
AS distance FROM Person_LatLong WHERE Person_Id IN 
(SELECT Watcher_Id FROM LS_For WHERE Subject_Id = 1 AND Assistance_Group_Id = 1)
HAVING distance < 5 ORDER BY Location_DateTime DESC;

Gives Below as Output :- ID | LAT | LNG | DATE TIME | DISTANCE

10  23.030301667271765  72.55715279051745   2014-09-02 04:27:21 0.037008818510632306
10  23.03029215373424   72.55710855670746   2014-09-02 04:25:09 0.03433299317128307
10  23.030211421184454  72.55705489668806   2014-09-02 04:23:42 0.02417068347133403

11  23.030301667271765  72.55715279051745   2014-09-02 05:27:21 0.037008818510632306
11  23.03029215373424   72.55710855670746   2014-09-02 05:25:09 0.03433299317128307
11  23.030211421184454  72.55705489668806   2014-09-02 05:23:42 0.02417068347133403

After Adding GROUP BY :-

SELECT *,( 6371 * acos( cos( radians(23.030) ) 
* cos( radians(Lat) ) * cos( radians(Lng) - radians(72.5570) )
 + sin( radians(23.030) ) * sin( radians(Lat) ) ) ) 
AS distance FROM Person_LatLong WHERE Person_Id IN 
(SELECT Watcher_Id FROM LS_For WHERE Subject_Id = 1 AND Assistance_Group_Id = 1)
GROUP BY Person_Id HAVING distance < 5 ORDER BY Location_DateTime DESC;

Gives me below Output :-

10  23.030211421184454  72.55705489668806   2014-09-02 04:23:42 0.02417068347133403

But I want to get latest time row not the oldest.

OUTPUT Required :- Working with 2 tables, LS_For is table where I am finding the Ids for which I have to look for the location distance and based on that need to filter the result based on distance.

10  23.030301667271765  72.55715279051745   2014-09-02 04:27:21 0.037008818510632306
11  23.030301667271765  72.55715279051745   2014-09-02 05:27:21 0.037008818510632306

Can you please FIDDLE you Schema & Sample Data? – AK47 Sep 18 '14 at 12:18 — AK47, Sep 18 '14 at 12:18

xQbert · Answer 1 · 2014-09-05T18:56:56.417

3

Generate a set of data containing the max date time (most recent) for each person and then INNER JOIN to it so that your base set islimited to only the most recent entry in person_LatLong.

(Select person_ID, max(`Location_DateTime`) mldt FROM person_LatLong group by Person_ID)

generates the most recent latLong for each user... So then...

POSSIBLE 1

SELECT a.Person_ID, a.LAT, a.LNG, a.`Location_DateTime`, 
 ( 6371 * acos( cos( radians(23.030) ) 
        * cos( radians(a.Lat) ) * cos( radians(a.Lng) - radians(72.5570) )
        + sin( radians(23.030) ) * sin( radians(a.Lat) ) ) ) AS Distance 
FROM Person_LatLong a
INNER JOIN (SELECT Person_ID, max(`Location_DateTime`) as mldt
            FROM Person_latLong 
            GROUP BY Person_ID) P
  on P.Person_ID = a.Person_Id
 and P.mldt = a.`Location_DateTime` 
LEFT JOIN LS_FOR C
 on a.Person_Id = C.Watcher_ID
  AND C.Subject_Id = 1 
  AND C.Assistance_Group_Id = 1
GROUP BY a.Person_ID, a.LAT, a.LNG, a.`Location_DateTime`
HAVING Distance < 5;

POSSIBLE 2

SELECT a.Person_ID, a.LAT, a.LNG, a.`Location_DateTime`, 
 ( 6371 * acos( cos( radians(23.030) ) 
        * cos( radians(a.Lat) ) * cos( radians(a.Lng) - radians(72.5570) )
        + sin( radians(23.030) ) * sin( radians(a.Lat) ) ) ) AS Distance 
FROM Person_LatLong a
INNER JOIN (SELECT Person_ID, max(`Location_DateTime`) as mldt
            FROM Person_latLong 
            GROUP BY Person_ID) P
  on P.Person_ID = a.Person_Id
 and P.mldt = a.`Location_DateTime` 
INNER JOIN LS_FOR C
 on a.Person_Id = C.Watcher_ID
WHERE C.Subject_Id = 1 
  AND C.Assistance_Group_Id = 1
GROUP BY a.Person_ID, a.LAT, a.LNG, a.`Location_DateTime`
HAVING Distance < 5;

I've amend the response to treat LS_FOR as a left join incase there are not records for each user in LS_FOR, and if not still return the person even though they wouldn't have a subject_ID or assistance_groupID....

In other words, The above should return in plain english:

The most recent personLatLong will be returned for each unique Person_ID; provided the distance is < 5 and if there is an entry in LS_FOR and that PersonLatLong entry, only records with a subjectID of 1 and asstianceGorupID of 1 will be returned. If there is no entry in LS_FOR it will still return the person (Perhaps you do want it as an inner join...)

edited Sep 05 '14 at 18:56

answered Sep 05 '14 at 13:32

xQbert

34,733
2
41
62

I am not MYSQL expert, I don't know at all about JOINS. So its very difficult for me to change your query. I am getting error right now. I am adding table structure as well in my question. – Scorpion Sep 05 '14 at 13:40
Added my table structure so that you get some idea. From your query I think you are matching date time but in my case I don't need to match date time. I just need to fetch Person_Id from LS_For table and based on those ids, I need to check location radius and display the best result. – Scorpion Sep 05 '14 at 13:49
Person_latLong table has multiple entries for a single user. In order to get the most recent, I'm simply joining person_latLong to a subset of itself in which only the most recent entry is listed. It should have 0 impact on the LS_For limits. INNER Joins by their nature may records whereas outer and inner joins just associate records. So by joining to just the subset on personId and location_datetime, we only get 1 record from person_latLong (most recent) – xQbert Sep 05 '14 at 14:00
I am getting latest entry but not all ids, its giving me only 1 row. – Scorpion Sep 05 '14 at 17:16
If you remove the where clauses and having does it return more than 1 record? If not, then LS_FOR doesn't contain a record with a watcher_ID of the Person_Id you're looking for. If it does, you having clause or where clause are eliminating expected results... – xQbert Sep 05 '14 at 17:33
Its having total 6 records, if i remove the Location_DateTime clause from the query. But if i add it I am getting only 1 record. So my where and having clauses are perfect. There is some minor issue in query. – Scorpion Sep 05 '14 at 17:50
Breaking it down: `SELECT Person_ID, max(`Location_DateTime`) as mldt FROM Person_latLong GROUP BY Person_ID` should return 1 record for each user containing the most recent location_datetime. Does it? If you eliminate the where clause, do you get the same number of records? If not, the Join to LS_FOR is the problem. maybe we need a left join... I'll update and amend for it as a possibility. – xQbert Sep 05 '14 at 18:52
Yes, I got each user with latest record using the above query. – Scorpion Sep 06 '14 at 04:21
We are very close to it but as I don't have much knowledge on DB I am unable to achieve what we need. – Scorpion Sep 06 '14 at 04:35
So if you eliminate the where clause and the having. and you only get 1 record then you have a DATA problem. If you are expecting 6 records, and only 1 is showing and we've eliminated the where and having clause the ONLY way we could get 1 record is if LS_FOR doesn't have matching records. – xQbert Sep 07 '14 at 19:47
Possible 1 is working but there is some minor issue in fetching ID from LS_For table. I am getting wrong person id from the table. If you fire - SELECT Watcher_Id FROM LS_For WHERE Subject_Id = 1 AND Assistance_Group_Id = 1 -> it will return 10, 11 & 17 as ID where as in this query its returning 1,2 & 10. – Scorpion Sep 11 '14 at 12:15
can you please guide me, we are very close to it. I am trying everything but not able to achieve what is needed. – Scorpion Sep 12 '14 at 03:58

score 0 · Answer 2 · answered Sep 05 '14 at 13:27

0

Instead of

GROUP BY Person_I

Use

LIMIT 1

OR

GROUP BY Person_Id HAVING distance < 5 ORDER BY Location_DateTime DESC;

to

GROUP BY Person_Id HAVING distance < 5 ORDER BY Location_DateTime ASC;

answered Sep 05 '14 at 13:27

Punitha Subramani

1,467
1
8
6

In that case it will give me only 1 result, where as I might get more than 1 Person_Id. Here I am not having only single Person_Id, i can have 10, 11, 12 in my result. – Scorpion Sep 05 '14 at 13:28
ok then you do order by ORDER BY Location_DateTime ASC; and do your group by... edited my post – Punitha Subramani Sep 05 '14 at 13:31
Not working. Its giving me only 1 row and I need multiple rows. – Scorpion Sep 05 '14 at 13:39
Nope, what am saying.. your coding is working you said but the problem is you are not getting latest datetime value. Because of GROUP BY fetching last row value, so you DESC to ASC it will work. – Punitha Subramani Sep 05 '14 at 13:40

score 0 · Answer 3 · edited May 23 '17 at 12:06

0

Check this link.

Retrieving the last record in each group

Here the solution is much faster than sub query.

   select person1.* from Person_LatLong person1 
    LEFT JOIN Person_LatLong person2 on person1.id = person2.id and 
   person1.dateTiem < person2.dateTime
    where person2.id IS NULL;

This will give you latest record.

edited May 23 '17 at 12:06

Community

1
1

answered Sep 05 '14 at 13:46

Charvee Shah

730
1
6
21

Are you suggesting a left join be used as the accepted answer suggests? (read some of the comments about what happens on larger sets of data and a 2nd reponse) – xQbert Sep 05 '14 at 13:51
I have already used that and it is working nice and much faster than sub query. – Charvee Shah Sep 05 '14 at 14:10
@CharveeShah I have seen that answer but as mentioned in my question I don't know DB well and specially the joins. I have 0 knowledge in it and that is the reason I am looking for some help here. In the link you provided I was unable to get everything and try lot of stuff from that but all failed. After that only I have posted question here. If you can help me by some guidance it would be great. – Scorpion Sep 05 '14 at 17:18
I doubt that this solution will outperform an uncorrelated sub query. Plus it's undocumented (unless you count the comments section of the manual) – Strawberry Sep 05 '14 at 19:08
@Scorpion so what do you want??I write the whole query? – Charvee Shah Sep 08 '14 at 05:47
I want to get 1 item from each group with latest date. If you see in my question I have written the query and current output as well as desired output. – Scorpion Sep 08 '14 at 06:35

score 0 · Answer 4 · answered Sep 17 '14 at 13:05

try doing this:

SELECT 
  *,( 6371 * acos( cos( radians(23.030) ) * cos( radians(Lat) ) * cos( radians(Lng) - radians(72.5570) ) + sin( radians(23.030) ) * sin( radians(Lat) ) ) ) AS distance
FROM 
  (select * from Person_LatLong ORDER BY Location_DateTime DESC) as t  
WHERE 
  Person_Id IN (SELECT Watcher_Id FROM LS_For WHERE Subject_Id = 1 AND Assistance_Group_Id = 1) 
GROUP BY 
  Person_Id 
HAVING 
  distance < 5 ;

Retrieve latest time row using Group by not working

4 Answers4