CODEWITHSUNDEEP
sqljoinmaxinner-join
DK_3
DK_3

Reputation: 3

SQL query to find the most recent timestamp for several ID's

I'm trying to run a query that returns the last 'game' or action that a certain group of users performed. I've had to join several tables to finally get what I want, but this basically returns each users games now. I'd like to just take their last game. I added max(___) to timestamp in the query but it ended up just returning the most recent game played and that is it. Ideally I'd like to see each user and their last action.

SELECT teamName, test_teams.userID, test_teams.timestamp AS signup, sponsorship, test_results.timestamp, stadium, win+draw+lose AS games 
    FROM test_teams 
INNER JOIN test_profile
    ON test_teams.userID = test_profile.userID
INNER JOIN test_results
    ON test_teams.userID = test_results.homeTeamId
WHERE test_teams.timestamp >= '2015-11-04 00:00:00' and win+draw+lose > 100
ORDER BY games desc;

Upvotes: 0

Views: 8237

Answers (1)

Alexander Pranko
Alexander Pranko

Reputation: 1969

The following SQL statement gives you the latest timestamp/action per team:

SELECT MAX(timestamp) as latestTimestamp, homeTeamId
  FROM test_results
  GROUP BY homeTeamId

It can be joined with the other 2 tables

SELECT teamName, tt.userID, tt.timestamp AS signup, sponsorship, tr.latestTimestamp, stadium, win+draw+lose AS games 
FROM test_teams tt
INNER JOIN test_profile
  ON test_teams.userID = test_profile.userID
INNER JOIN (
  SELECT MAX(timestamp) as latestTimestamp, homeTeamId
  FROM test_results
  GROUP BY homeTeamId) tr 
  ON test_teams.userID = tr.homeTeamId

Upvotes: 1

Related Questions