Clever Geek Handbook

Sqlite order by random groups

I have a table of songs with columns of tracks and albums and I want to get them in random album order. that is, in each album, the songs will be sorted, but the albums will be in random order.

For example, if the table:

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

what is the possible way out

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

Can a single sqlite query be executed?

+3


source to share


2 answers


I needed to add an order to MrSimpleMind's answer to achieve the order I wanted:



WITH albums AS (SELECT DISTINCT album FROM songs ORDER BY RANDOM()) 
SELECT songs.* FROM albums
LEFT JOIN songs
ON songs.album = albums.album
ORDER BY albums.rowid, songs.track
0


source


First you need to randomly order albums and then select 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

+3


source






More articles:


All Articles