CODEWITHSUNDEEP
mysql
Dean
Dean

Reputation: 517

mysql, distinct and by most recent

I'm using query:

SELECT distinct player, date, (health_base + mana_base * 2) as vit FROM dadb.player_attributes order by  vit desc

and getting the following results:

# player       date,                   vit
-----------------------------------------------
'Greenslade', '2016-01-12 14:05:03',  '1208252'
'Greenslade', '2016-01-12 14:05:04',  '1208252'
'Greenslade', '2016-01-12 14:07:46',  '1208252'
'Greenslade', '2016-01-12 14:07:47',  '1208252'
'Pheresis',   '2016-01-12 13:45:54',  '924164'
'Pheresis',   '2016-01-12 13:45:55',  '924164'
'Pheresis',   '2016-01-12 13:48:55',  '924164'
'Pheresis',   '2016-01-12 13:48:56',  '924164'
'Pheresis',   '2016-01-12 13:48:57',  '924164'
'Pheresis',   '2016-01-12 14:04:00',  '924164'
'Pheresis',   '2016-01-12 14:04:01',  '924164'

How can I make it so my sql query returns only one player, date, vit; for the each distinct player with the most recent date?

I want my output to be:

# player       date,                   vit
-----------------------------------------------
'Greenslade', '2016-01-12 14:07:47',  '1208252'
'Pheresis',   '2016-01-12 14:04:01',  '924164'

how can i do it?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You simply need aggregation:

select player, max(date), max(health_base + mana_base * 2) as vit
from t
group by player
order by vit desc;

Upvotes: 2

Related Questions