Selecting last 5 positions of all users from a join

Question

I have 2 tables: player_positions (records the gps coordinates of all players) and players (records data for each player).

players
player_id, player_name

player_positions
player_id, player_lat, player_lon, timestamp

I want to get last 5 positions for all players.

My first query for selecting the last locations of one player is:

SELECT * FROM player_positions WHERE player_id = 1 ORDER BY timestamp DESC LIMIT 5

But I don't know how to duplicate this query to extract all players data. I believe I have to do a select within a previous join but not sure how to write it.

Thank you.

@Adrian: have a look at http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867 — VMai, May 05 '14 at 17:34
@VMai I think I must be doing something wrong as the moment I add in the inequality I get 0 results back... I'll keep on trying :) — Adrian, May 05 '14 at 17:45

echo_Me · Answer 1 · 2014-05-05T17:51:54.187

1

Try this :

  SELECT pp.* , p.player_name 
  FROM player_positions pp 
  INNER JOIN  players p ON p.player_id = pp.player_id
  GROUP BY pp.player_id 
  ORDER BY pp.timestamp DESC LIMIT 5

EDIT:

 SELECT pp.* , p.player_name
 FROM player_positions pp
 INNER JOIN players p ON p.player_id = pp.player_id
 LEFT OUTER JOIN player_positions pp2 ON pp.player_id = pp2.player_id AND pp.timestamp < pp2.timestamp
 GROUP BY pp.player_id
 HAVING COUNT(*) < 5
 ORDER BY pp.timestamp DESC LIMIT 5

edited May 05 '14 at 17:51

answered May 05 '14 at 17:23

echo_Me

37,078
5
58
78

1

that would produce 5 records (max) but not 5 for each player. – VMai May 05 '14 at 17:24
Thanks for the response but it seems to be bringing back only one result per player – Adrian May 05 '14 at 17:29
Sorry for not being clear, I was looking to get last 5 positions for all players, so that if i have 3 players I should get 15 results back. Thx – Adrian May 05 '14 at 17:32
Unfortunately, still no go. It doesn't bring any results back and if I take the HAVING COUNT (*) < 5, I only get the same data as with your previous example. Thx – Adrian May 05 '14 at 17:57
have you a fiddle example ? – echo_Me May 05 '14 at 17:58
Unfortunately not. Sorry. – Adrian May 05 '14 at 18:02

Conrad Frix · Accepted Answer · 2014-05-05T21:52:12.050

You can emulate the rownumber function found in other dbs like so, to solve this problem.

SELECT 
    player_id,
    player_lat,
    player_lon,
    `timestamp`
FROM 
    (SELECT 
        pp.player_id, 
        pp.player_lat,
        pp.player_lon,
        pp.`timestamp`,
        @rn := if(@prev = pp.player_id, @rn + 1,1) as rn,
        @prev:=pp.player_id

    FROM
        Player_Positions as pp
        JOIN (SELECT @Prev:= Null, @Rn := 0) as v
    ORDER BY 
        pp.player_id,
        pp.timestamp desc) as t
WHERE
    rn <= 5;

Note that if for some reason there's a tie for 5th the database will arbitrarily pick one. Since it's unlikely that a player can be in two positions for the same timestamp you should be okay.

This is brilliant, exactly what I was looking for. I haven't used user defined variables before but I think I understand how it works. Thank you! — Adrian, May 06 '14 at 09:01

Selecting last 5 positions of all users from a join

2 Answers2