sqlite order by random groups

Question

I have a table of songs with track and album columns and I want to get them in a random album order. i.e. such that in each album the songs will be sorted but the albums will be in random order.

For example, if the table is:

-----------------
| track | album |
-----------------
| 1     | a     |
| 2     | a     |
| 3     | a     |
| 1     | b     |
| 2     | b     |
| 1     | c     |
| 2     | c     |
-----------------

than a possible output will be

-----------------
| track | album |
-----------------
| 1     | b     |
| 2     | b     |
| 1     | c     |
| 2     | c     |
| 1     | a     |
| 2     | a     |
| 3     | a     |
-----------------

Can it be done with a single sqlite query?

Answer 1

First you need to order the albums randomly, and then select the tracks.

with tt as (
  select distinct album from t order by random()
)
select t.track, tt.album 
from tt left join t on t.album = tt.album
order by tt.rowid, t.track
;

See Sqlfiddle