mardi 4 avril 2017

SQL to select randomly intercalated grouped results

For a table that has ID and category columns we will have a category domain with 5 values, for example A, B, C, D and E.

We are using SQLite.

Our goal is to have a query that brings a number of intercalated grouped rows randomly selected and randomly sorted (inside each group of 5 rows), such as

ID    CAT
---   ---
076    C
753    D
503    A 
081    B 
335    E

475    B
643    D
995    A
105    E
549    C

553    E
871    B
064    C
720    D
119    A

The example above shows 3 groups os results, whose rows were randomly selected and whose sorting is also random.

Currently we are pulling the results using 5 queries (one for each category), and then sorting everything on application code.

SELECT * FROM table WHERE cat = 'A' ORDER BY RANDOM() LIMIT 3;

We will appreciate it if someone suggests a better solution by using a single query.




Aucun commentaire:

Enregistrer un commentaire