CODEWITHSUNDEEP
sqlpostgresql-9.4zabbix
Thierry Regis
Thierry Regis

Reputation: 11

sql : select first row on any change in an order by

Hello on postgres I want to do this following task

I have this request :

select to_timestamp(clock), value
from history_str
where itemid =696857
order by clock desc

which returns this :

enter image description here

I'd like to get the fist occurence of the list order by the clock .

the result I expect is then point 1 to 8 with timestamp and value.

I've tried with the over function but cannot manage to finalize it correctly.

Any help ? Thanks

Upvotes: 1

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You want to use lag() for this, because value can change back to a previous value:

select to_timestamp(clock), value
from (select t.*,
             lag(value) over (partition by itemid order by to_timestamp(clock)) as prev_value
      from t
      where itemid = 696857
     ) t
where prev_value is null or prev_value <> value
order by clock desc;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

One approach uses ROW_NUMBER:

SELECT ts, value
FROM
(
    SELECT TO_TIMESTAMP(clock) AS ts, value,
        ROW_NUMBER() OVER (PARTITION BY value ORDER BY TO_TIMESTAMP(clock) DESC) rn
    FROM history_str
) t
WHERE rn = 1;

Upvotes: 1

Related Questions