CODEWITHSUNDEEP
sqlpostgresql
Dmitriy_pechatnik
Dmitriy_pechatnik

Reputation: 37

LAG sql or no? postgresql

I have a table, I want to display one previous record

select id_1 
       , lag(kd_1) over(order by kd_1) as ID_PREV
       , kd_1
       , dt_change
       , id_usr 
from t1.status_hist 
where kd_sys_1 = 6
order by id_sys_1 


1   19  19  2020-12-01 14:35:40 id_usr
1   18  18  2020-12-01 14:35:49 id_usr
2   19  19  2020-12-01 14:35:50 id_usr
3   18  18  2020-12-01 14:38:14 id_usr
3   19  19  2020-12-01 14:37:27 id_usr
4   19  19  2020-12-01 14:37:54 id_usr
4       18  2020-12-01 14:38:14 id_usr

But I only need one entry and what would happen something like this

1   18  19  2020-12-01 14:35:40 id_usr
2   19  19  2020-12-01 14:35:50 id_usr
3   18  19  2020-12-01 14:37:27 id_usr

only the previous entry

Upvotes: 2

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

In Postgres, you would use distinct on:

select distinct on (id_1) sh.*
from t1.status_hist sh
where kd_sys_1 = 6
order by id_1, dt_change desc;

The above does not return the previous entry. I interpreted that part of the question as being an attempt to get the latest entry. If you really want that in the result set, just use a subquery:

select distinct on (id_1) sh.*,
       lag(kd_1) over (order by kd_1) as id_prev
from (select sh.*
      from t1.status_hist sh
      where kd_sys_1 = 6
     ) sh
order by id_1, dt_change desc;

Upvotes: 1

Related Questions