MySQL: How to get the maximum disposition and the maximum date from my database

Question

I want to get the top disposition of every ID where 1 is the lowest and 13 is the highest, however when there are IDs with similar disposition (e.g. ID #2 having multiple dispositions of '9') I cannot get the record with the latest time / timestamp.

How can I group the results by ID getting the maximum disposition and if there are multiple disposition, get the most recent? (e.g. ID #2 getting '2017-04-06 14:16:00' as the top or max.)

edit: I forgot to mention that I also need to group them by date. I have a separate column for date. The expected output is: Latest + Highest dispo of ID #2 on April 6 and Latest + Highest dispo of ID#2 on April 7 and so forth.

edit2:

Answer 1

I've set up the next example:

create table docs(id int, dispo int, dt date, st timestamp);

insert into docs values
(1, 10, '20170406', '2017/04/06 01:30:00'),
(1, 5 , '20170407', '2017/04/07 02:30:00'),
(1, 10, '20170406', '2017/04/06 01:00:00'),
(1, 10, '20170406', '2017/04/06 01:15:00'),
(2,  9, '20170406', '2017/04/06 01:30:00'),
(2,  9, '20170407', '2017/04/07 13:45:00'),
(2,  9, '20170407', '2017/04/07 09:00:00'),
(2,  9, '20170407', '2017/04/07 10:45:00');

First you need to know the MAX disposition of every date:

SELECT   id ID, dt "Date", max(dispo) Disposition
FROM     docs
GROUP BY id, dt

ID | Date       | Disposition
-: | :--------- | ----------:
 1 | 2017-04-06 |          10
 1 | 2017-04-07 |           5
 2 | 2017-04-06 |           9
 2 | 2017-04-07 |           9

Then you can obtain MAX Stamp by disposition and date.

SELECT   t1.id ID, 
         t1.dispo Disposition, 
         t1.dt "Date", 
         max(t1.st) Stamp
FROM     docs t1
JOIN     (SELECT   id, dt, max(dispo) as dispo
          FROM     docs
          GROUP BY id, dt) t2
ON        t1.id = t2.id
AND       t1.dispo = t2.dispo
AND       t1.dt = t2.dt
GROUP BY  t1.id, t1.dispo, t1.dt
ORDER BY  t1.id, t1.dt;

ID | Disposition | Date       | Stamp              
-: | ----------: | :--------- | :------------------
 1 |          10 | 2017-04-06 | 2017-04-06 01:30:00
 1 |           5 | 2017-04-07 | 2017-04-07 02:30:00
 2 |           9 | 2017-04-06 | 2017-04-06 01:30:00
 2 |           9 | 2017-04-07 | 2017-04-07 13:45:00

dbfiddle here

Answer 2

This approach uses a correlated subquery to find the max timestamp...

select q1.id,
       q1.dispo,
       q1.date,
       (select max(stamp) from table_a where id=q1.id and dispo=q1.dispo and date=q1.date) as max_stamp
  from (select id, max(dispo) as dispo, date
          from table_a
         group by id, date) as q1;

And this approach uses a join...

select q1.id,
       q1.dispo,
       q1.date,
       max(a.stamp) as max_stamp
  from (select id, max(dispo) as dispo, date
         from table_a
         group by id, date) as q1
  join table_a a on (a.id=q1.id and a.dispo=q1.dispo as a.date=q1.date)