postgresql - How to create a new column with a label for every 5 rows in sql?

Question

I want to create a new column in my table. I want this new column to give the label "done" for every 5 rows.

My table looks like this:

no  type
1    a
2    a 
3    a
4    a
5    a
6    a 
7    a
8    a
9    a
10   a

and what I mean with the new column is:

no  type   flag
1    a    
2    a 
3    a
4    a
5    a      done
6    a 
7    a
8    a
9    a
10   a      done

so the "done" label will always appear for every 5 rows.

I use postgres for this. How can I do that with query?

Thanks in advance.

question from:https://stackoverflow.com/questions/65915916/how-to-create-a-new-column-with-a-label-for-every-5-rows-in-sql

Answer 1

You can use the row_number() function and the modulo operator:

select no, type, 
       case 
         when row_number() over (order by no) % 5 = 0 then 'done'
       end as flag
from the_table
order by no;