mysql - Filter for most recent client version in SQL table -

so have database , have used select/join statements try create leaderboard based on number of games users of app have played, along information on device.

my issue database contains multiple rows 1 user if have downloaded multiple release versions of app. table looks like:

+--------+---------+----------+-------------+ |# games | user id | platform | app version | +--------+---------+----------+-------------+ |   15   |   1     |     ios  |   3.2.1     | +--------+---------+----------+-------------+ |   13   |   2     | android  |   2.0.3     | +--------+---------+----------+-------------+ |   13   |   2     | android  |   3.2.1     | +--------+---------+----------+-------------+ |   13   |   2     | android  |   3.1.0     | +--------+---------+----------+-------------+ |   11   |   3     |     ios  |   3.1.5     | +--------+---------+----------+-------------+ 

is there way consolidate each unique user id multiple rows (from having used multiple versions) 1 row, contains info on recent version? is, above table consolidated :

+--------+---------+----------+-------------+ |# games | user id | platform | app version | +--------+---------+----------+-------------+ |   15   |   1     |     ios  |   3.2.1     | +--------+---------+----------+-------------+ |   13   |   2     | android  |   3.2.1     | +--------+---------+----------+-------------+ |   11   |   3     |     ios  |   3.1.5     | +--------+---------+----------+-------------+ 

if want details row, filter on max (or min or something) on 1 column can in many different ways.

one way find max app version rows each user in derived table join filter rows returned, option use correlated not exists query checks there doesn't exists row same user later app version (and same platform).

the 2 queries aren't same first doesn't take platform account, if want can add derived table, group , join.

select t1.*  your_table t1 inner join (   select `user id`, max(`app version`) max_app_version   your_table    group `user id` ) t2  on t1.`user id` = t2.`user id`  , t1.`app version` = t2.max_app_version;   select t1.*  your_table t1 not exists (   select 1 your_table    `app version` > t1.`app version`   , `user id` = t1.`user id`   , platform = t1.platform ); 

sample sql fiddle