Tournament Mode – SQL Playlist

[Axilla]

I posted this yesterday and it actually posted.  Afterwards, I went to edit the post and it removed the thread.  Mostly likely due to SQL injection prevention, but odd it took it the first time.  In any event, I’m trying again just in case anyone finds it useful.

We’re a very competitive bunch over here when it comes to pinball, but we always seem to pick a lot of the same tables to beat up on each other (our head-to-head scoring system is another thread for another day). With the ability to use active SQL lists, it’s allowed us to make things a lot more competitive. I created an active playlist that will give us 20 random tables ordered by the least number of times played to ensure no one will have an advantage of having mastered the table. Here’s what I’m using for our “Grand Tournament” playlist (and I’ll explain as best I can):

select g.*, sum(case when d.numplayed is null then 0 else d.numplayed end) as Played
from games g
left join playlistdetails d on g.gameid = d.gameid
where g.visible = 1
group by g.gameid, g.emuid, g.gamename, g.gamefilename
order by Played, random()
limit 20

1. Since I’m using multiple tables, I prefer to use table aliases so I don’t have to continually type out the table names. The alias is listed after the table name (the “g” after games in my “from” statement)
2. I didn’t know fields would be required from the games table, so I just bring in all fields (g.*).
3. To get the number of times played, this information is stored in the PlayListDetails table. However, since the tables may never have been played, you must use a “left join” which means everything from the table in the previous statement “games” and matching records from the “left join” table “PlayListDetails”.
4. Since there may not be records in the PlayListDetails table, I check for a null value in my “select” statement and replace that with a zero since I know that a null value means it hasn’t been played (I’m sure there is a variant isnull() function that would be a lot simpler than my case statement, but I’m used to writing everything in MSSQL and isnull() is not a valid sqlite function).
5. I then sum up all of those numbers because the PlayListDetails table is grouped by PlayListID. Tables could exist in multiple playlists, but I simply want all unique tables.
6. Lastly, I order my results by my number of times played count first, then by “random()”. This way, if I have 100 tables that have been played zero times, it will give us a random selection from those 100. Once I only have 10 tables left that haven’t been played, this will return me the 10 tables that haven’t been played before in a random order and then 10 tables that have been played once in a random order.

A couple additional notes:

• You could limit this by g.emuid if you only want it for certain emulators. I only have VP on my cab, so I haven’t done that.
• You could also limit this for the number of played based on which playlist it was played in, but that’s a takes it a step further and I won’t go into that detail right now (we use this to keep the counts based on the group who played, not as a total number of played by table).

Explaining technical things has never been one of my strongest suites, so I can probably answer specific questions a lot better than trying to explain this in one long thought that makes sense in my own head.

Thanks again to NailBuster for this awesome software! Everyone loves the new tournament mode!

You need to login in order to like this post: click here

[Axilla]

FYI – Just a bit more additional info for anyone wanting to post their SQL, using the HTML < c o d e > < / c o d e > (no spaces) tag in your post should help prevent the post from getting canned due to potential SQL injection attack prevention measures.

You need to login in order to like this post: click here

[NailBuster]

i think  the case can be replaced with

sum(coalesce(d.numplayed,0)) as Played

but you’d have to check…

You need to login in order to like this post: click here

[Author]

Posts