Game Design, Programming and running a one-man games business…

SQL headache

cliffski

Gah, I spent all morning wracking my brains to fix a rendering order batching bug (fixed! yay!), and now I suddenly have a second daily headache with some SQL. If you use SQL a lot you can probably tell me how trivial this is…

Imagine a table of scores

Dave  PlanetA 4,200

Mike PlanetB 2,200

Dave PlanetA 4,100

Chas Planet A, 7,200

And so on. What I am doing right now is a SELECT to get the top 20 rows from this table where the planet is (for example ) PlanetA. What I *want*n to do is to get the top 20 rows, but only the TOP entry for each player. So you only appear once in each high score list.

I can’t get my head around how to do that. I want something like SELECT * FROM scores WHERE scores.planet == ‘zog’ AND scores.score IS HIGHEST FOR score.player ORDER BY score

But of course I’ve made that syntax up and its imaginary. Albeit cool.

Tell me there is an easy way to do this thang?

And ooh guest what! Democracy 3 is the #1 strategy game on the ipad right now, due to a rather insane price drop I’m experimenting with…

9 thoughts on SQL headache

  1. SELECT name, max(score) as top_score FROM scores GROUP BY name ORDER BY top_score DESC

    2. Also, since you want just the top 20 rows. Now this depends on the database engine and not standard, but you can add “LIMIT 20” (most databases) or “AND rowno < 21" (Oracle) to the end of the query. That saves the DB from fetching up all the rows, and you from keeping track of it.

  2. select player, planet, max(score) from scores where planet = ‘zog’ group by planet, player order by max(score) desc

  4. I don’t know much about game development, but I know a lot about SQL. Ping me if you need anymore help with queries like this.

    Sam

  5. Grouping is not always the best solution. It will often work fine for relatively small amounts of data but for larger volumes of data I have found the following to be more efficient.

    Select top 20 PlayerId, PlayerName, PlanetId, PlanetName, Score
    From (Select PlayerId, PlayerName, PlanetId, PlanetName, Score, RANK() OVER (PARTITION BY PlayerId ORDER BY Score DESC) AS Rank
    From ScoreTable
    where PlanetId = xxxxx) as PScores
    Where Score.Rank = 1
    Order By Score Desc

    That is the basic part, you will also want to have a look at your indexes as these can improve performance by orders of magnitude.
    You’d probably be better separating out the PlayerName and PlanetName to loading them separately – Planets could probably be cached in memory, while loading 20 players based on ID should be fast).
    For indexes, you could start with an Index that covers [PlanetId, PlayerId, Score], then if you do include the Player and Planet names in the table, have them as ‘included’ columns in the index. Depending on how much data you have and whether these are all the fields or not, this could either make if very fast, or no fast than a table scan!

    I have also found for high score scenarios, if you only need to get the top 20 (or that makes up most of the score queries) then keep a memory cache of the 20th score for each planet, then add ‘ and score >= 12345)’ to the inner SQL part to discard most of the irrelevant scores (this may also require you to change the order of the fields in the Index)

    The above is based on MS SQL, not sure how much of that functionality is present in other SQL systems.

    More info on SQL Rank Over: https://msdn.microsoft.com/en-us/library/ms176102.aspx

    1. Sorry, mistake in the SQL, should have been

      Select top 20 PlayerId, PlayerName, PlanetId, PlanetName, Score
      From (Select PlayerId, PlayerName, PlanetId, PlanetName, Score, RANK() OVER (PARTITION BY PlayerId ORDER BY Score DESC) AS Rank
      From ScoreTable
      where PlanetId = xxxxx) as PScores
      Where PScores.Rank = 1
      Order By Score Desc

  6. That “experiment” gives and the lack of demos these days sort of gives the idea of selling the demo first at low price and then even lower price promotion, and then sell the full game as dlc/mod … I still think game devs should focus on the game engine and platform development and leave everything which isn’t related to engineering to.. umm non-engineers. eg. Bathesdas games/engines make for good setting for story telling but the company isn’t using the right people to make the story,writing,scripting etc so the result is quite reliably something that looks like AAA game but the experience isn’t AAA.

    In other news Valve backed out from the paid mods stuff. That stuff really needs to be thought ahead of time (not thrown into existing game) and probably the paid stuff needs to be completely original as things get mind numbingly complicated if you can take some other peoples free mod, modify it and the ask money for your modification. It’s nice to think about paid collaboration but someone is always going to end up unhappy because perhaps the first original mod was unpopular and the modded mod is popular – now the original mod maker still wants most of the money probably. Then the mod morphs into something that doesn’t look/play anything like the original – should the original author still get all the money for no work? The age old question in the creative industry.

Comments are currently closed.