0

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:

enter image description here enter image description here

0

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

  • 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. – B. Ellana Apr 7 '17 at 10:23
  • Please add a good example in your question of your desired result. – McNets Apr 7 '17 at 11:05
  • Thank you for helping. I have provided a sample date for you to check. – B. Ellana Apr 7 '17 at 12:10
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.