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

Question

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...

Answer 1

The easiest way is with a temporary table (which you might not want to make temporary if you want to play those songs over a long time). The INTEGER PRIMARY KEY column is automatically filled with autoincrementing values:

CREATE TEMPORARY TABLE AlbumOrder(Album, Sort INTEGER PRIMARY KEY);

INSERT INTO AlbumOrder(Album)
SELECT DISTINCT Album
FROM Songs
ORDER BY random();

SELECT Album, Track
FROM Songs
JOIN AlbumOrder USING (Album)
ORDER BY Sort, Track;

Answer 2

I think that you just need to order by Album before of random :

SELECT album, track FROM songs ORDER BY album, random()