CODEWITHSUNDEEP
sql-servert-sql
LAffair
LAffair

Reputation: 1998

SQL query select geography point

I'm new in SQL querying and I need to get the last position of some players that are active, meaning they have Play value equal to 1. I have to make a join between the table Events where I have the players activity with columns:

with the table Positions where I have all players position at every 2-3 seconds with columns:

in order to get the current latitude and longitude of the players that are active.

My current query:

select 
    e.PlayerId, e.Active,
    p.Location.Lat, p.Location.Long,
    max_date = max(e.Timestamp),
    max_date2 = max(p.Timestamp)
from
    Events e 
inner join 
    Positions p on e.PlayerId = p.PlayerId 
where 
    e.Active= 1
group by 
    e.PlayerId, e.Active, p.Location.Lat, p.Location.Long

but instead of returning 2 rows I get much more. I guess it's because of the b.Location.Lat, b.Location.Long fields inside the group by clause because the simple query:

select 
    e.PlayerId, e.Active,
    max_date = max(e.Timestamp),
    max_date2 = max (p.Timestamp)
from 
    Events e 
inner join 
    Positions p on e.PlayerId = p.PlayerId 
where 
    e.Active = 1
group by 
    e.PlayerId, e.Active

returns the correct 2 rows but I need to also get the Lat-Long columns.

UPDATE I found an issue inside my query. When I've run it again for different values I've seen that it returns all the players position if they were even only once active and after that they got inactive. But if the last value for Active (according to the maximum timestamp) for one user is 0, then the query should remove the player location from the response.

Is there any way that I can add those columns without getting more rows than needed?

Upvotes: 1

Views: 484

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

You could wrap your current query in an outer query, then join to your positions table again. Something like this;

SELECT
base.PlayerId
,base.Active
,base.max_date
,base.max_date2
,p.Location.lat
,p.Location.long
FROM
(
    SELECT a.PlayerId ,
       a.Active,
       max_date = max( a.Timestamp ),
       max_date2 = max (b.Timestamp)
    FROM Events a 
    INNER JOIN Positions b 
        ON a.PlayerId =b.PlayerId 
    WHERE a.Active= 1
    GROUP BY a.PlayerId , a.Active
) base
JOIN Positions p
    ON base.PlayerId = p.PlayerId
    AND base.max_date2 = p.Timestamp

The reason your other query wasn't working is that you're going to have an entry for each lat & long point. Doing this will give you the unique list that you're after, then joins to Positions again to get lat long info.

Edit: As per the comments, if you want to exclude anybody with the latest Active value set to zero then add this to the end of the code;

JOIN
(
    SELECT
    e.PlayerID
    ,MAX(e.Timestamp) Timestamp
    FROM Events e
    GROUP BY e.PlayerID
) latest
ON base.PlayerID = latest.PlayerID
JOIN Events e2
ON latest.PlayerID = e2.PlayerID
AND latest.Timestamp = e2.Timestamp

WHERE e2.Active <> 0

Upvotes: 1

Related Questions