MySQL getting maximum of column with fallback to second column

Question

+------+---------+--------+---------+---------+---------+
| id   | user_id | obj_id | created | applied | content |
+------+---------+--------+---------+---------+---------+
|    1 |       1 |      1 |       1 |       1 | ...     |
|    2 |       1 |      2 |       1 |       1 | ...     |
|    3 |       1 |      1 |       1 |       2 | ...     |
|    4 |       1 |      2 |       2 |       2 | ...     |
|    5 |       2 |      1 |       1 |       1 | ...     |
|    6 |       2 |      2 |       1 |       1 | ...     |
+------+---------+--------+---------+---------+---------+

I have a table similar to the one above. id, user_id and obj_id are foreign keys; created and applied are timestamps stored as integers. I need to get the entire row, grouped by user_id and obj_id, with the maximum value of applied. If two rows have the same applied value, I need to favour the maximum value of created. So for the above data, my desired output is:

+------+---------+--------+---------+---------+---------+
| id   | user_id | obj_id | created | applied | content |
+------+---------+--------+---------+---------+---------+
|    1 |       1 |      1 |       1 |       1 | ...     |
|    4 |       1 |      2 |       2 |       2 | ...     |
|    5 |       2 |      1 |       1 |       1 | ...     |
|    6 |       2 |      2 |       1 |       1 | ...     |
+------+---------+--------+---------+---------+---------+

My current solution is to get everything ordered by applied then created:

select * from data order by applied desc created desc;

and sort things out in the code, but this table gets pretty big and I'd like an SQL solution that just gets the data I need.

➕1 for the ASCII art; but for the sake of CVE: please add your initial query. — Linus Kleen, Jul 29 '16 at 18:23
@LinusKleen How do you mean? The query I'm currently using, or the queries to replicate the above data? — Simon Brahan, Jul 29 '16 at 18:24
Do the fact you want group by Which value do you want select for content .. ? — ScaisEdge, Jul 29 '16 at 18:54
@scaisEdge The content is just some other fields; I'll take them out. I need a row for every user_id for every obj_id. — Simon Brahan, Jul 29 '16 at 19:11
Consider the highest rated [answer to this question](http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results), it should get you halfway there. — Mark H., Jul 29 '16 at 19:16

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Thanks to Mark Heintz in the comments, this answer got me to where I need to be.

SELECT
    data.id,
    data.user_id,
    data.obj_id,
    data.created,
    data.applied,
    data.content
FROM data
LEFT JOIN data next_max_applied ON
    next_max_applied.user_id = data.user_id AND
    next_max_applied.obj_id = data.obj_id AND (
        next_max_applied.applied > data.applied OR (
            next_max_applied.applied = data.applied AND
            next_max_applied.created > data.created
        )
    )
WHERE next_max_applied.applied IS NULL
GROUP BY user_id, obj_id;

Go read the answer for details on how it works; the left join tries to find a more recently applied row for the same user and object. If there isn't one, it will find a row applied at the same time, but created more recently.

The above means that any row without a more recent row to replace it will have a next_max_applied.applied value of null. These rows are filtered for by the IS NULL clause.

Finally, the group by clause handles any rows that have identical user, object, applied and created columns.

score 0 · Answer 2 · answered Jul 30 '16 at 05:01

select * 
from my_table
where id in (
  /* inner subquery b */
  select max(id) 
      from my_table where 
      (user_id, obj_id, applied, created) in (
          /* inner subquery A */
          select user_id, obj_id, max(applied), max(created) 
          from my_table 
          group by user_id, obj_id
      )
);

Then inner subquery A return the (distinct) rows having user_id, obj_id, max(applied), max(created). Using these with in clause the subquery B retrive a list of single ID each realated the a row with a proper value of user_id, obj_id, max(applied), max(created). so you have a collection of valid id for getting your result.

The main select use these ID for select the result you need.

MySQL getting maximum of column with fallback to second column

2 Answers2