CODEWITHSUNDEEP
sqlitegroup-by
Neal Bailey
Neal Bailey

Reputation: 183

SQLite Group By Limit

I have a web service that generates radio station playlists and I'm trying to ensure that playlists never have tracks from the same artist more than n times.

So for example (unless it is Mandatory Metallica --haha) then no artist should ever dominate any 8 hour programming segment.

Today we use a query similar to this which generates smaller randomized playlists out of existing very large playlists:

SELECT FilePath FROM vwPlaylistTracks 
WHERE Owner='{0}' COLLATE NOCASE AND 
Playlist='{1}' COLLATE NOCASE 
ORDER BY RANDOM()
LIMIT {2};

Someone then has to manually review the playlists and do some manual editing if the same artist appears consecutively or more than the desired limit.

Supposing the producer wants to ensure that no artist appears more than twice in the span of the playlist generated in this query (and assuming there is an artist field in the vwPlaylistTracks view; which there is) is GROUP BY the correct way to accomplish this?

I've been messing around with the view trying to accomplish this but this query always only returns 1 track from each artist. 

SELECT 
   a.Name as 'Artist',
   f.parentPath || '\' || f.fileName as 'FilePath',
   p.name as 'Playlist',
   u.username as 'Owner'
FROM mp3_file f, 
     mp3_track t, 
     mp3_artist a, 
     mp3_playlist_track pt, 
     mp3_playlist p,
     mp3_user u
WHERE f.file_id = t.track_id
AND t.artist_id = a.artist_id
AND t.track_id = pt.track_id
AND pt.playlist_id = p.playlist_id
AND p.user_id = u.user_id
--AND p.Name = 'Alternative Rock'
GROUP BY a.Name
--HAVING Count(a.Name) < 3
--ORDER BY RANDOM()
--LIMIT 50;

Upvotes: 0

Views: 1706

Answers (1)

CL.
CL.

Reputation: 180091

GROUP BY creates exactly one result record for each distinct value in the grouped column, so this is not what you want.

You have to count any previous records with the same artist, which is not easy because the random ordering is not stable. However, this is possible with a temporary table, which is ordered by its rowid:

CREATE TEMPORARY TABLE RandomTracks AS
SELECT a.Name as Artist, parentPath, name, username
FROM ...
WHERE ...
ORDER BY RANDOM();
CREATE INDEX RandomTracks_Artist on RandomTracks(Artist);

SELECT *
FROM RandomTracks AS r1
WHERE -- filter out if there are any two previous records with the same artist
      (SELECT COUNT(*)
       FROM RandomTracks AS r2
       WHERE r2.Artist = r1.Artist
         AND r2.rowid < r1.rowid
      ) < 2
  AND -- filter out if the directly previous record has the same artist
      r1.Artist IS NOT (SELECT Artist
                        FROM RandomTracks AS r3
                        WHERE r3.rowid = r1.rowid - 1)
LIMIT 50;

DROP TABLE RandomTracks;

It might be easier and faster to just read the entire playlist and to filter and reorder it in your code.

Upvotes: 2

Related Questions