CODEWITHSUNDEEP
pythonsqlpostgresql
student
student

Reputation: 541

SQL, adding the same sequential number to every 1000 rows

I have a table, which I order by amounts of entries per user. Then I copy the values from this table and I would like to give a sequential number to every 1000 rows. So if one user has 2000 entries and it is the highest amount, then his entries get the values 1 and 2 (it doesn't matter which of his entries get 1 and which 2). If the next two users have 501 and 499 entries, all of their entries get the values 3 and so on. The problem is that I am not sure where to start, I have seen the "select top x" solution, but it doesn't help here. That is what I have for now, so I need to insert a condition of "every 1000 sequential rows", but I don't know which direction I should search for it.

cur.execute("SELECT * FROM table1 ORDER BY users_count DESC;")
dbrows = cur.fetchall()
conn.commit()
my_number = 1
for "every 1000 sequential rows" in dbrows:
    cur.execute("INSERT INTO table2 (users_count, number) VALUES (%s,%s)", (row[0],my_number))
    my_number += 1

Upvotes: 0

Views: 82

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Why Python? You can do that in a SQL INSERT/SELECT like this: 

insert into table2 (users_count, number) 
select users_count, 
   ((row_number() 
     over (order by ORDER BY users_count DESC) - 1) / 1000) + 1 
from table1

This creates a sequential number and then uses some integer division to assign the same number to group of 1000 rows.

Upvotes: 3

Related Questions