Question 221445 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Put Sequence ID from specific date

I’m trying to put sequence id which should start from current date then backwards and should only limit up until 5 rows. I can’t do by date as there are times there are missing date row so I still have to follow the current date to whatever date is next backwards

I already came up with a query but it seems that it doesn’t really start with current date backwards. Any other approach I can do?

SELECT *,
ROW_NUMBER() over (partition by employee_id order by date rows 5 preceding exclude current row)
from employee table

enter image description here

Tags:

2

Answers


  1. 0

    Try the following query:

    Example with sample data:

    Temp Table:

    create temp table temp_emp 
( 
    employee_id int,
    date date
);

    Sample data:

    insert into temp_emp values(1,'2023-08-02');
insert into temp_emp values(2,'2023-08-01');
insert into temp_emp values(3,null);
insert into temp_emp values(4,'2023-07-31');
insert into temp_emp values(5,'2023-07-29');
insert into temp_emp values(6,null);
insert into temp_emp values(7,'2023-08-03');
insert into temp_emp values(8,'2023-07-03');
insert into temp_emp values(9,'2023-01-01');
insert into temp_emp values(10,null);

    Query:

    ;with cte as
(
    SELECT *,
    ROW_NUMBER() over (partition by (case when date <=  now() then 1 else null end) order by date desc) rnk
    from temp_emp
)
select date, case when rnk <= 5 and date <=now() then rnk else null end as ranks
from cte;

    Result:

    enter image description here

    Login or Signup to reply.
  2. 0

    I think you also need to list employee columns such as employee id(you did select *). If so, you need something like this:

    select main.* from (select distinct emp from test ) t1,
  lateral(select emp, dt, row_number() over (partition by emp order by dt desc) rn
  from test t2 where t1.emp = t2.emp
  and t2.dt <= now()
  group by t2.emp, t2.dt
  order by t2.dt desc
  limit 5) main
  order by 1, 2 desc;

    Inside of the lateral join you get last 5 date for each group and then iterate it.

    Fiddle

    Also, there is a different version of it:

    with main as (select emp, dt from test
group by emp, dt
  having dt <= now()), final as(
  select emp, dt, row_number() over (partition by emp order by dt desc) rn
  from main where dt <= now())
  select * from final where rn <= 5;

    In order to compare them I increased the data volume and you can see comparison of explain output for both queries using fiddle.

    In short, second query is faster.

    Note: there were mistakes on the answer, they were fixed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search