RADIODJ.INFO || TUTORIALS.
SELECT * FROM ( SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`id_subcat`, 1 AS `preference` FROM `songs` LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`) WHERE `songs`.`enabled` = 1 AND `songs`.`song_type` = 0 AND `songs`.`id_subcat` = 44 AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01') AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01') AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$) AND `queuelist`.`artist` IS NULL LIMIT 1 UNION SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`date_played`, `songs`.`id_subcat`, 2 AS `preference` FROM `songs` LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`) WHERE `songs`.`enabled` = 1 AND `songs`.`song_type` = 0 AND `songs`.`id_subcat` = 25 AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01') AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01') AND (TIMESTAMPDIFF(MINUTE, `songs`.`date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `songs`.`artist_played`, NOW()) > $ArtistRepeatInterval$) AND `queuelist`.`artist` IS NULL ORDER BY `date_played` LIMIT 2 )temp ORDER BY `preference` LIMIT 1;
SELECT `songs`.`ID`, `songs`.`artist`, `songs`.`title`, `songs`.`id_genre` FROM `songs` LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`) LEFT JOIN ( SELECT `songs`.`id_genre` FROM `queuelist` LEFT JOIN `songs` ON (`songs`.`ID` = `queuelist`.`songID`) ORDER BY queuelist.ID DESC LIMIT 2 ) AS queue_genre ON (songs.id_genre = queue_genre.id_genre) WHERE `songs`.`enabled` = 1 AND `songs`.`song_type` = 0 AND `songs`.`id_subcat` = 44 AND (`songs`.`start_date` <= NOW() OR `songs`.`start_date` = '2002-01-01 00:00:01') AND (`songs`.`end_date` >= NOW() OR `songs`.`end_date` = '2002-01-01 00:00:01') AND (TIMESTAMPDIFF(MINUTE, `date_played`, NOW()) > $TrackRepeatInterval$) AND (TIMESTAMPDIFF(MINUTE, `artist_played`, NOW()) > $ArtistRepeatInterval$) AND `queuelist`.`artist` IS NULL AND queue_genre.id_genre IS NULL ORDER BY `date_played` ASC LIMIT 1
Note: You can get top whatever played only if history table contains data for the time period you need.
-- Select Top10 tracks from July 2016 SELECT songs.ID, songs.artist, songs.title, COUNT(*) AS spins, SUM(history.listeners) as total_listeners FROM `songs` LEFT JOIN `history` ON (songs.title = history.title AND songs.artist = history.artist) -- This is the date range filter WHERE history.date_played BETWEEN '2016-07-01' AND '2016-08-01' AND songs.song_type = 0 GROUP BY history.title, history.artist ORDER BY spins DESC LIMIT 10;