0

I have a table called Stats in my database:

| Game_ID | User_ID |  Rank | Creation_date |
---------------------------------------------
|       1 |       1 |    1  |    2017-04-03 |
|       1 |       2 |    2  |    2017-04-03 |
|       1 |       3 |    3  |    2017-04-03 |
|       1 |       1 |    4  |    2017-05-03 |

And I currently use the following query to fetch all rows for a specific Game_ID:

"SELECT * FROM Stats WHERE Game_ID = 2 ORDER BY Rank ASC"

This returns exactly what is shown above, I would however like to return only one row per User_ID (The one with the most recent Creation_date), like this:

| Game_ID | User_ID |  Rank | Creation_date |
---------------------------------------------
|       1 |       2 |    2  |    2017-04-03 |
|       1 |       3 |    3  |    2017-04-03 |
|       1 |       1 |    4  |    2017-05-03 |

Any help would be appreciated!

EDIT

I tried the solution above, and I'm certain it is the right one. I dont get duplicates anymore of the User_ID. However, I don't get the latest Creation_date. What am I missing?

The updated query:

SELECT a.Game_ID, a.User_ID, a.rank, a.Creation_date
            FROM stats a
            INNER JOIN (
                SELECT User_ID, MAX(Creation_date), Creation_date
                FROM stats
                WHERE Game_ID = 2
                GROUP BY User_ID
            ) b ON a.User_ID = b.User_ID AND a.Creation_date = b.Creation_date ORDER BY rank ASC;

Returns:

| Game_ID | User_ID |  Rank | Creation_date |
---------------------------------------------
|       1 |       1 |    1  |    2017-04-03 |
|       1 |       2 |    2  |    2017-04-03 |
|       1 |       3 |    3  |    2017-04-03 |

In other words, not the row with the most recent Creation_date for User_ID 1.

Stim
  • 113
  • 8

1 Answers1

-1

Try this - 

SELECT Game_ID, User_ID, MAX(Rank), Max(Creation_date)
FROM Stats 
WHERE Game_ID = 2
GROUP BY Game_ID, User_ID, Creation_date
ORDER BY Rank, Creation_date
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40