mercredi 24 juin 2015

How to sort SELECT results randomly, but in "groups" (NOT grouped by)

I have an SQLITE database with info about music files - i.e. with album, track columns (and some more, but for simplicity I will leave them out).

[table SONGS]
ALBUM     TRACK
A1        1
A1        2
A2        1
A2        2
A3        1
A3        2
A4        1
A4        2

I know I can do SELECT album, track FROM songs ORDER BY random(), but I will get the albums scrambled like:

ALBUM     TRACK
A4        2
A3        2
A2        1
A1        1
A3        1
A1        2
A2        2
A4        1

And I want to randomize the order, but keep the albums together, like:

ALBUM     TRACK
A3        1
A3        2
A2        1
A2        2
A1        1
A1        2
A4        1
A4        2

How can I do that? I tried many things (with an additional table with SELECT DISTINCT album FROM songs ORDER BY random()), but none of that worked.

I know I can do that in code (I'm writing in XOJO), but I want to use SQLITE to do that...




Aucun commentaire:

Enregistrer un commentaire