CODEWITHSUNDEEP
postgresqlrandom
Mick McCabe
Mick McCabe

Reputation: 1

Postgres function to attach random integers to selected rows

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?

Upvotes: 0

Views: 181

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions