CODEWITHSUNDEEP
phpmysql
Stefan Dinkelberg
Stefan Dinkelberg

Reputation: 38

Getting list of scores from a certain stage and level

For my game I am trying to include an online score system. For my score system to work i want to get a list of scores from my database. But since I'm not that experienced with mysql statementes, I have no clue how to write a query for what i want.

The structure of my table: http://prntscr.com/6z45bv

In my query i want to return all the colums in my my table "scores"

    SELECT * from scores

with only the the stage and level given with a GET request in php

    WHERE stage = [number] AND level = [number]

with my request I would like to get the highest single score from all devices sorted from highest to lowest. the devices are stored in the "deviceid" column

The highest score from a device is the highest amount of "blastpoints"(descending) with the highest amount of "time"(descending). Where the highest amount of blastpoints are the most important.

Here is an example of what i would want as a result:

    id, name,      stage,  level,  blastpoints,  time, badge, deviceid

    5,  "bob"      2       3       5             7123  true   "asdakjhe"
    1,  "rick"     2       3       5             6321  false  "3jkhasdl3"
    8,  "melissa"  2       3       4             8565  false  "nlk43h2l"
    2,  "steven"   2       3       4             5678  true   "342jhlad"
    3,  "freddy"   2       3       3             6721  false  "1lk23jl1"
    7,  "hank"     2       3       2             5600  false  "41asdg42"

In this example i retrieved the scores from stage 2, level 3. as you can see the blastpoints are descending and it only finds the highest score of a single deviceid.

The scores are set up that a player with a certain deviceid can upload as many scores as he wants in the table.

I hope I gave enough information, for further questions please comment. And thanks in advance.

Upvotes: 0

Views: 72

Answers (3)

Stefan Dinkelberg
Stefan Dinkelberg

Reputation: 38

After testing Augwa's query for a while, I figured out it was not working. I did some trial and error myself and found the correct query.

SELECT A.name,A.blastpoints,A.time,A.badge,A.deviceid
FROM (SELECT B.id,B.name,B.blastpoints,B.time,B.badge,B.deviceid
    FROM scores B
    WHERE B.stage = 0
    AND B.level =  0
    ORDER BY blastpoints DESC, time DESC) A
GROUP BY A.deviceid
ORDER BY A.blastpoints DESC, A.time DESC

Upvotes: 0

Jonathan
Jonathan

Reputation: 2877

What you need to do is group by. Group by works on uniqueness so there are certain fields that you can't group by as they will simply cause problems. The id field for instance you could not do a group by, but you can get the min(), max() of it.

The below query will show you the top scores for each device on each level and stage.

SELECT
    stage,
    level,
    deviceid,
    max(blastpoints) score
FROM
    scores
GROUP BY
    stage,
    level
    deviceid
ORDER BY
    level ASC,
    stage ASC,
    score DESC,
    deviceid

This query should allow you to consider time as well.

SELECT
    min(s.id) id,
    s.stage,
    s.level,
    hs.score,
    s.time,
    s.badge,
    s.deviceid
FROM
    scores s
INNER JOIN
    (
        SELECT
            stage,
            level,
            deviceid,
            max(blastpoints) score
        FROM
            scores
        GROUP BY
            stage,
            level
            deviceid
    ) hs ON
        hs.deviceid = s.deviceid
        AND hs.score = s.blastpoints
        AND hs.stage = s.stage
        AND hs.level = s.level
WHERE
    s.stage = :stage
    AND s.level = :level
GROUP BY
    s.level,
    s.stage,
    hs.score,
    s.time,
    s.badge,
    s.deviceid
ORDER BY
    s.level ASC,
    s.stage ASC,
    hs.score DESC,
    s.time,
    s.deviceid

Upvotes: 1

Muhammad Abdul-Rahim
Muhammad Abdul-Rahim

Reputation: 2010

In SQL you can use ORDER BY to sort your results. In this case, since you would need:

ORDER BY blastpoints DESC, time DESC

We use DESC for descending order. That way the largest scores appear at the top. The order in which you specify column names in ORDER BY matters as well, which is why we specify blastpoints first and time second, as per your example. You can read more on ORDER BY, too.

Putting it all together:

SELECT * FROM scores
WHERE stage = [number] AND level = [number]
ORDER BY blastpoints DESC, time DESC

Upvotes: 2

Related Questions