CODEWITHSUNDEEP
mysqlstored-proceduresgreatest-n-per-group
Ben
Ben

Reputation: 11

How to update top two rows of a table in stored procedure

I'm on this exercise:

Create a single routine that sums up all the points scored by players of each class, displays the result with the sum and the group name ordered from highest to lowest, and finally adds two points to the first group and one point to the second group.

and I'm having a lot of trouble making sense and getting somewhere with the "finally adds two points to the first group and one point to the second group". I assume it's asking to add 2 to the top 1 score and add 1 to the top 2 score, the problem is I can't figure out how to do that.

I've only managed to get to the following point:

CREATE PROCEDURE  total_scores()
SQL SECURITY DEFINER
BEGIN
        SELECT SUM(scores) AS 'total scores', class
    FROM players
    GROUP BY class
    ORDER BY SUM(scores) DESC LIMIT 2 OFFSET 0;
END //

Upvotes: 1

Views: 41

Answers (2)

ysth
ysth

Reputation: 98398

This is pretty straightforward. Here I'm using class to disambiguate ties:

select
    score_sum + case ranking when 1 then 2 when 2 then 1 end score,
    class
from (
    select
        class,
        sum(scores) score_sum,
        row_number() over (order by sum(scores) desc, class) ranking
    from players
    group by class
) class_scores
where ranking < 3
order by score desc

Most cases of doing more complicated things with ties can be handled by replacing row_number() with rank() or dense_rank() and changing the case statement and possibly removing the where condition and adding a limit to the outer query. Details depend on what you want.

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76943

Actually you need all groups and to add 2 to the first place and 1 to the second place. I will give you only a query (untested, as you gave no schema nor sample data):

WITH rankings AS
(
    SELECT SUM(scores) AS 'total_scores', class
    FROM players
    GROUP BY class
)
SELECT total_scores +
        CASE
            WHEN COUNT(*) = 1 THEN
                CASE
                    WHEN MAX(better.class) IS NULL THEN 2
                    ELSE 1
                END
            ELSE 0
        END
     as 'total scores',
     class
FROM rankings current
LEFT JOIN rankings better
ON current.class <> better.class AND
   current.total_scores <= better.total_scores
GROUP BY current.class

If there are typos or other mistakes, then let me know and we can fix it.

Explanation:

  • we first define the query that we will reuse and name it rankings
    • we select from players
    • grouped by class
    • we sum the scores and for the space of this definition we do not add the bonus points yet and we select class too
  • we select from rankings and we alias it as current
  • we left join with rankings, aliased as better
  • on the criteria that their class does not match
  • and current.total_scores <= better.total_scores
  • grouped by current.class, so from now on aggregates can be used
  • in the select clause
    • we compute total_scores
      • by getting its inherent value
      • and if we have a single record in the group (that is, either the best or the second-best scorer)
        • then, if there are no better classes, i.e., better.class is always null, then we add 2
        • otherwise it's the second place and we add 1
      • otherwise this is not the first nor the second, so we add 0
      • and alias the result as total scores, as you preferred
    • and select class too

Note that this allows ties, but, if there is a tie of three, then none of them will get extra points, whereas if there is a single winner but a tie at second place, then only the winner gets extra points, if there is a tie of two, then each of them gets one extra point, otherwise the first place gets 2 extra points and the second place gets 1 extra point.

Upvotes: 0

Related Questions