Shafizadeh Shafizadeh - 6 months ago 9
MySQL Question

How to get greatest time when there is an aggregate function?

I have a table like this:

// mytable
+----+------+-------+-------------+
| id | type | score | unix_time |
+----+------+-------+-------------+
| 1 | 1 | 5 | 1463508841 | -- this (current output)
| 2 | 1 | 10 | 1463508842 |
| 3 | 2 | 5 | 1463508843 | -- this (current output)
| 4 | 1 | 5 | 1463508844 |
| 5 | 2 | 15 | 1463508845 | -- this (expected output)
| 6 | 1 | 10 | 1463508846 | -- this (expected output)
+----+------+-------+-------------+


And here is my query:

SELECT SUM(score), unix_time
FROM mytable
WHERE 1
GROUP BY type


And this is current output:

+-------+-------------+
| score | unix_time |
+-------+-------------+
| 30 | 1463508841 |
| 20 | 1463508843 |
+-------+-------------+


As you see, the output is containing the first row's
unix_time
. But I'm trying to select greatest one.

So here is expected output:

+-------+-------------+
| score | unix_time |
+-------+-------------+
| 30 | 1463508846 |
| 20 | 1463508845 |
+-------+-------------+


How can I do that?

Answer

I think you need sum(score) and max(unix_time) for the grouped value

SELECT type, SUM(score), max(unix_time)
FROM mytable
GROUP BY type

I omitted where 1 because seems non sense ..