Question 20834 in Mysql Question posted in
The official MySQL documentation can be found here.

Select the oldest record of a certain group until it changes pattern, in SQL – Mysql

I am trying to get the oldest record for every status update/change in the following table.

Table (status_updates) :

id entity_id status date
7 2 Approved 2022-02-10
6 2 Approved 2022-02-05
5 2 Approved 2022-02-04
4 2 OnHold 2022-02-04
3 2 OnHold 2022-02-03
2 2 Approved 2022-02-02
1 2 Approved 2022-02-01

Result Needed :

id entity_id status date
5 2 Approved 2022-02-04
3 2 OnHold 2022-02-03
1 2 Approved 2022-02-01

Tried :

select
    `status`,
    `created_at`
from
    `status_updates`
left join
    (select
        `id`,
        row_number() over (partition by status_updates.entity_id, status_updates.status order by status_updates.created_at asc) as sequence
    from
        `status_updates`)
    as `oldest_history`
    on
        `oldest_history`.`id` = `shipper_credit_histories`.`id`
where `sequence` = 1

Result Achived :

id entity_id status date
3 2 OnHold 2022-02-03
1 2 Approved 2022-02-01
Tags:

3

Answers


  1. 0

    here are the queries:

    create table status_updates
(entity_id integer,
status varchar(32),
date date
);

insert into status_updates values (2, 'Approved', '2022-02-05');
insert into status_updates values (2, 'Approved', '2022-02-04');
insert into status_updates values (2, 'On Hold', '2022-02-04');
insert into status_updates values (2, 'On Hold', '2022-02-03');
insert into status_updates values (2, 'Approved', '2022-02-02');
insert into status_updates values (2, 'Approved', '2022-02-01');

select b.*
from status_updates a
right join status_updates b 
on a.status=b.status and a.date=(b.date - interval 1 day) 
where a.entity_id is null;

    or this query(if you prefer left join)

    select a.*
from status_updates a
left join status_updates b
on a.status=b.status and a.date=(b.date + interval 1 day)
where b.entity_id is null;

    in both you will see the expected result

    Login or Signup to reply.
  2. 0

    the second solution is almost the same, but join by id instead of date

    create table status_updates
(id integer,
entity_id integer,
 status varchar(32),
 date date
);

insert into status_updates values (7, 2, 'Approved', '2022-02-10');
insert into status_updates values (6, 2, 'Approved', '2022-02-05');
insert into status_updates values (5, 2, 'Approved', '2022-02-04');
insert into status_updates values (4, 2, 'On Hold', '2022-02-04');
insert into status_updates values (3, 2, 'On Hold', '2022-02-03');
insert into status_updates values (2, 2, 'Approved', '2022-02-02');
insert into status_updates values (1, 2, 'Approved', '2022-02-01');

select a.*
from status_updates a
         left join status_updates b
                   on a.status=b.status and a.id=b.id + 1
where b.entity_id is null;

    result is the same what you expected

    Login or Signup to reply.
  3. 0

    Just using lag:

    select s.*
from (
    select id, status<>coalesce(lag(status) over (partition by entity_id order by id),'') status_change
    from status_updates
) ids
join status_updates s using (id)
where status_change
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search