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

Postgresql – How to number rows 1,1,1,1,2,2,2,2 in SQL

I am looking to number the rows of my output with four 1’s, then four 2’s etc etc regardless of the value in the rows. My data is sorted by week so I am basically trying to group the weeks into groups of four.

I have tried looking at row_number() but can’t figure out how to do this.
Any help appreciated.

desired output

Tags:

2

Answers


  1. 0 
    select WeekCode, Revenue, 1 + (row_number() over (order by WeekCode) - 1) / 4 from Data
    Login or Signup to reply.
  2. 0

    select (rn/4)+1-if (rn%4)=0 then 1 else 0 end if Grouping,* from (select row_number() over(order by id desc) rn ,WeekCode,Revenue from my_table order by id desc)foo

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

Back To Top
Search