lundi 12 octobre 2015

Assign a random order to each group

I want to expand each row in TableA into 4 rows. The result hold all the columns from TableA and two additional columns: SetID = ranging from 0 to 3 and unique when grouped by TableA. Random = a random permutation of SetID within the same grouping.

I use SQLite and would prefer a pure SQL solution.

Table A:

Description
-----------
A
B

Desired output:

Description | SetID | Random
------------|-------|-------
A           |     0 | 2
A           |     1 | 0
A           |     2 | 3
A           |     3 | 1
B           |     0 | 3
B           |     1 | 2
B           |     2 | 0
B           |     3 | 1

My attempt so far

SELECT
  Description,
  SetID,
  abs(random()) % 4 AS wrong
FROM
  TableA
LEFT JOIN
  TableB
ON
  1 = 1

Table B:

SetID
-----
0
1
2
3

Aucun commentaire:

Enregistrer un commentaire