Round Robin - sgf-web-devs/tap-music GitHub Wiki

Saving out some queries I have been playing around with. Just don't want to lose them

/*
got this from tweaking info here
http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql
*/
SELECT id,
       spotifyURL,
       trackName,
       artistName,
       albumArt,
       userID,
       userName,
       userImage,
       broadcasting
FROM
  (SELECT songs.*, @rn := if(userID != @ng, 1, @rn + 1) AS ng, @ng := userID
   FROM songs,
     (SELECT @rn:=0, @ng:=NULL) v
   ORDER BY userID,
            id) sq
WHERE broadcasting = FALSE
ORDER BY ng,
         id,
         userID

/*
tried getting the same results from a different angle...
a little testing yields the same result.. more verbose 
but I feel like I can read and tweak easier
*/
SET @counter = 0;
SET @currentUser = "";
SET @lastUser = "";
SELECT id,
       spotifyURL,
       trackName,
       artistName,
       albumArt,
       userID,
       userName,
       userImage
FROM
  ( SELECT *, @currentUser:=userID AS currentUser, 
    @counter:= CASE
                  WHEN @lastUser=@currentUser THEN @counter+1
                  ELSE @counter:=1
                  END AS rr, 
    @lastUser:=userID AS lastUser
   FROM
     ( SELECT *
      FROM songs
      ORDER BY userID ) t) r
ORDER BY rr, id