-1

How to get first occurrence of last changed value of "sval" column? for id = 22, 71 is the last changed value so wants to fetch first occurence of 71. same way for id = 25, 74 is the last changed value so wants to fetch first occurence of 74.

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=c980809154d41f2accc9f14d569b48f1

data:

enter image description here

in above picture i wanted to fetch highlighted row.

try:

with LastValue as (
  select t.sval
  from test t 
  order by t.date desc 
  limit 1
)
select t.*
from test t
where t.sval = (select sval from LastValue)
  and t.date > (select max(tt.date) from test tt where tt.sval <> (select sval from LastValue))
order by t.date asc
limit 1;

actually the problem statement is i dont want the group by sval first occurence, instead i want the whatever last changed sval so our example after highlighted rows should be returns for ids (22,25).

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Juned Ansari
  • 5,035
  • 7
  • 56
  • 89
  • If the values for some `id` are 1,2,1,1 then what 1 must be returned - the former or pre-last? – Akina Aug 25 '21 at 10:17

2 Answers2

2
WITH
cte1 AS ( SELECT *, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn1,
                 ROW_NUMBER() OVER (PARTITION BY id, sval ORDER BY `date` DESC) rn2
          FROM test ),
cte2 AS ( SELECT *, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` ASC) rn3
          FROM cte1
          WHERE rn1 = rn2 )
SELECT id, date, sval
FROM cte2
WHERE rn3 = 1;

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=a25569690e4b35a55b0bee13856eb724

Akina
  • 39,301
  • 5
  • 14
  • 25
  • hey @Akina, how about multiple id's ? i have added 1 more id in that case it does not work eg. https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=b063661f69e4da94ddbd9ef68b52c82e – Juned Ansari Aug 25 '21 at 10:43
  • last changed value of id = 26 is 70 and its first occurence will be "select 26 as id, '2021-07-29' as date, 70 as sval union all" – Juned Ansari Aug 25 '21 at 10:58
  • @JunedAnsari This is an effect of incorrect data - the `date` contains duplicates for added `id` (3 rows with `date='2021-07-30'`, and 2 different `sval` values in these rows). You **must** have an expression which allows to sort your rows within the group with the same `id` uniquely! – Akina Aug 25 '21 at 10:59
  • @JunedAnsari *last changed value of id = 26 is 70* Prove that last value for `date='2021-07-30'` is 70 and not 72. Remember - physical posession is not a reason. – Akina Aug 25 '21 at 11:00
  • last changed value for id=26 is 70 right but its first occurence will be at date='2021-07-29' – Juned Ansari Aug 25 '21 at 11:04
  • @JunedAnsari I see the row with the date of `'2021-07-30'` and the value of `72`. Why you ignore its existence? According to your comment to the question - the last changing to `70` from `72` which occures at '2021-07-30' is met at `'2021-07-31'`. – Akina Aug 25 '21 at 11:05
  • adter 72 last changes is 70 and its first occurence will be date= '2021-07-29' – Juned Ansari Aug 25 '21 at 11:08
  • sorry its not pre-last it is first occurence – Juned Ansari Aug 25 '21 at 11:09
  • 1
    @JunedAnsari *adter 72 last changes is 70 and its first occurence will be date= '2021-07-29'* ??? Please show/write **precise** expression for ORDER BY clause for which the first occurence of 70 in the last block of adjacent rows with this value have date= '2021-07-29'. While using `ORDER BY id, date` this is not correct, the row with `('2021-07-30', 72)` exists and posesses between the rows `('2021-07-29', 70)` and `('2021-07-31', 70)`, so `'2021-07-29'` is not correct. – Akina Aug 25 '21 at 11:27
  • sorry, date re repeated thats is why date = '2021-07-30' returns that is correct – Juned Ansari Aug 25 '21 at 12:12
1

One method for doing this uses lag() to check for a difference and then chooses the last point where there is a difference:

select t.*
from (select t.*,
             row_number() over (partition by id order by date desc) as seqnum
      from (select t.*,
                   lag(sval) over (partition by id order by date) as prev_sval
            from test t
           ) t
      where prev_sval is null or prev_sval <> sval
     ) t
where seqnum = 1;

Very importantly: This returns the last time there was a change even when the value returns to an already seen value for the id. That is how I interpret your question.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • hey @Gordon Linoff, how about multiple id's ? i have added 1 more id in that case it does not work eg. https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=96ffea70debce53d8645eeb6ada91056 – Juned Ansari Aug 25 '21 at 10:43
  • last changed value of id = 26 is 70 and its first occurence will be "select 26 as id, '2021-07-29' as date, 70 as sval union all" – Juned Ansari Aug 25 '21 at 10:58
  • 1
    @JunedAnsari . . . This works for multiple `id`s. Your fiddle has duplicate dates, and if you have duplicate dates, you should ask a new question and be very clear on how they should be resolved. In this question you specifically say "first occurrence" which implies that the dates correctly identify the ordering. – Gordon Linoff Aug 25 '21 at 11:52