Postgres function to attach random integers to selected rows

Question

I want a function or a trigger that when a row is inserted that all rows with matching criteria are given a random integer between 1 and the number of rows so to randomise the rows on a select.

E.g. if I have the data

Col1    Col2   Order
  A     1
  B     2
  B     2
  B     3
  A     2

and I insert another row with Col1=B and Col2=2 then I want to end up with

Col1    Col2   Order
  A     1
  B     2      2
  B     2      3
  B     3
  A     2
  B     2      1

Where Order is a number with a value of 1 - with each number appearing only once?

user330315 · Accepted Answer · 2013-01-21 15:22:42Z

1

There is no need to store this, you can generate such a number when you retrieve the data.

select col1, 
       col2, 
       row_number() over (partition by col1, col2 order by random()) as random_order
from the_table

answered Jan 21, 2013 at 15:22

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres function to attach random integers to selected rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related