0

This is my first question here, couldn't find any idea on how to solve this.

I have an MySQL Table which looks like this:

ID|uuid    |Value        |World
#1|1       |10           |World1 
#2|2       |11           |World1 
#3|1       |9            |World2 
#4|3       |13           |World2

There are two of them. One for login and one for logout. Value represents the time -> originaly its saved in UNIX time.

I can't change the tables, so I need to work with this layout.

My question is, how can I create a view, which gives me only the last login/logout time for each player?

In the example it should give me for Player 1 only the 10h.

I used this one:

select `lj`.`uuid` AS `uuid`,`lj`.`value` AS `ljValue`,`ls`.`value` 
AS `lsValue`,`sp`.`name` AS `name` 
from ((`Stats3_last_join` `lj` 
left join `Stats3_last_seen` `ls`
on((`lj`.`uuid` = `ls`.`uuid`))) 
left join `Stats3_players` `sp` 
on((`lj`.`uuid` = `sp`.`uuid`)))

But I get multiple entries for each player. I need something like DISTINCT with a condition.

Dmitry Shvedov
  • 3,169
  • 4
  • 39
  • 51
monjo44
  • 31
  • 8
  • Possible duplicate of [MySQL: Get most recent record](http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record) – xQbert Feb 12 '16 at 20:28

1 Answers1

0

One method is to use an inline view to create a subset of data which consists of the max value for each player then join it back to the base set to get additional values if needed.

SELECT AR.* 
FROM stats3_last_join AR
INNER JOIN (SELECt max(value) mv, uuid 
            FROM stats3_last_Join
            GROUP BY uuid) MaxOnly
 on MaxOnly.mv= AR.Value
and MaxOnly.uuid = AR.Uuid

So in this case we return max value for each uuid in stats3_lastJoin. then join it back to itself to based on this max value and uuid to limit the results to be only the max value.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Thank you! I had to create two new views instead of using the inline select, cause views dont accept them somehow. But now it's working :) – monjo44 Feb 12 '16 at 21:10