DaDu DaDu - 6 months ago 22
MySQL Question

mysql. I want to get the max value of all rows with a specific date and use the corresponding fields

My table 'players_online'

id | count | date | time | day | max-players

0 | 55 | 26/05/16 | 13:00 | 5 | 300
1 | 33 | 26/05/16 | 13:10 | 5 | 300
2 | 43 | 26/05/16 | 13:20 | 5 | 300
3 | 100 | 27/05/16 | 13:00 | 6 | 300
4 | 43 | 27/05/16 | 13:10 | 6 | 300
5 | 56 | 27/05/16 | 13:20 | 6 | 300


desired output (todays highest count)

id | count | date | time | day | max-players

3 | 100 | 27/05/16 | 13:00 | 6 | 300


Also

(yesterdays highest count)

id | count | date | time | day | max-players

0 | 55 | 26/05/16 | 13:00 | 5 | 300


And

(Higest total count)

id | count | date | time | day | max-players

3 | 100 | 27/05/16 | 13:00 | 6 | 300


My knowledge of mysql has gotten rusty and I have no idea how to get it right. previously I tried the following which didn't select the right time.

SELECT MAX(count) AS maxcount, date, time FROM players_online WHERE date='".date('d-m-y')."'


I'm building a little stats page where you can view the highest player count of a minecraft server today (until now), yesterday and the highest player count of all time with their corresponding date, time, day and max-players.

Hope somebody can give me a hand.

Answer

You can just do a simple query that orders by count and is limited to 1.

SELECT * FROM players_online WHERE date = '27/05/2016' ORDER BY count DESC LIMIT 1

That should get you the highest count.