-1

I have a table with 1 million+ records like this:

![enter image description here

My aim is to get records that have status N, excluding those records that do not have the latest (wrt date column) entry as Y. I'm looking for something like this:

enter image description here

Here entries with status=N are id=123,129,128. The latest entry associated with order 129 is 'Y' so we exclude it from our output. In other words, because 129 had status N previously, but on its new run on the next day, the status changed to Y. So I do not need 129 for further processing.

I tried with nested SELECT queries and join but was unable to get the right combination. Kindly suggest a suitable solution.

R.K
  • 1,721
  • 17
  • 22
  • 1
    Latest in what grouping? By date? – Barmar Mar 01 '23 at 17:55
  • What have you already tried and where exactly are you stuck? Are you familiar with ["top 1 per group"](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) solutions? – PM 77-1 Mar 01 '23 at 17:57
  • Write a subquery that gets the latest row for each date. Join that with the original table, and filter to `latest.status = 'Y'` – Barmar Mar 01 '23 at 17:59
  • @Barmar - Good point! I somehow immediately assumed `by id` . – PM 77-1 Mar 01 '23 at 18:00
  • Updated the question as per recommendation. – R.K Mar 01 '23 at 18:01
  • *excluding those records that do not have the latest (wrt date column) entry as Y* means: "including those records that have the latest (wrt date column) entry as Y". Then why do you exclude 129? – forpas Mar 01 '23 at 18:07
  • @forpas ... because 129 had status N previously. But on its new run on the next day, the status has been changed to Y. So I do not need 129 for further processing. – R.K Mar 01 '23 at 18:13
  • Edit your question and clarify. Your requirement is unclear as it is. – forpas Mar 01 '23 at 18:15
  • I'm guessing that `L` is a reference to an Indian word. Please use measurements that everyone understands. – James Z Mar 01 '23 at 18:15
  • Edits are done as per suggestions. – R.K Mar 01 '23 at 18:24
  • Still the same comment, *Lakh* is not used or understood globally. Please don't use local words here. – James Z Mar 01 '23 at 18:55

1 Answers1

1

Using window function row_number() to order data by date, then we check if the latest row of the status_id is 'N' or not.

   with cte as (
      select t.*, row_number() over (partition by id order by Date ) as row_id
      from mytable t
      order by id
    ),
    cte2 as (
      select id, max(row_id) as max_row_id
      from cte
      group by id
    )
    select c.id, c.Date, c.status
    from cte c
    inner join cte2 c2 on c.row_id = c2.max_row_id and c.id = c2.id
    where c.status = 'N';

For old versions of mysql/mariadb :

select t.id, t.Date, t.status
from mytable t
inner join (
  select id, max(Date) as max_Date
  from mytable
  group by id
) as c on c.max_Date = t.Date and c.id = t.id
where t.status = 'N';

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29