How to select two max value from different records that has same ID for every records in table

Question

i have problem with this case, i have log table that has many same ID with diferent condition. i want to select two max condition from this. i've tried but it just show one record only, not every record in table.
Here's my records table:

order_id           seq    status
____________________
1256                2           4
1256                1           2
1257                0           2
1257                3           1

Here my code:

WITH t AS(
SELECT x.order_id
    ,MAX(y.seq) AS seq2
    ,MAX(y.extern_order_status) AS status
FROM   t_order_demand x
JOIN  t_order_log y
ON    x.order_id = y.order_id
where x.order_id like '%12%'
GROUP  BY x.order_id)
SELECT *
FROM   t
WHERE  (t.seq2 || t.status) IN (SELECT MAX(tt.seq2 || tt.status) FROM t tt);

this query works, but sometime it gave wrong value or just show some records, not every records. i want the result is like this:
order_id           seq2    status
____________________
1256                2           4
1257                3           2

Answer 1

I think you just want an aggregation:

select d.order_id, max(l.seq2) as seq2, max(l.status) as status
from t_order_demand d join
     t_order_log l
     on d.order_id = l.order_id
where d.order_id like '%12%'
group by d.order_id;

I'm not sure what your final where clause is supposed to do, but it appears to do unnecessary filtering, compared to what you want.