0

So, I'm managing a table where it's stored the scores of a particular competition.

The table looks like this:

ENTRY_ID    TEAM_ID     DATE        PLACE       SCORE
1           1           2021-10-12  Ireland     64
2           2           2021-10-12  Ireland     31
3           3           2021-10-12  France      137
4           2           2021-10-12  France      61
5           5           2021-10-12  France      38
6           1           2021-10-12  France      66
7           2           2021-10-12  Italy       17
8           3           2021-10-12  Italy       61
9           1           2021-10-12  Italy       74

The competition is held at three different places at the same time, with technically all teams being able to have teams in all of them.

Each team however can only win one point so, in the example, it's possible to see that Team 1 would win both in Italy and Ireland, but it should be awarded only one point for the highest score, so only Italy. The point in Ireland should go to the second place.

The query I was trying to get the results is:

SELECT `TEAM_ID`, `PLACE` 
FROM `COMPETITION`
WHERE `date` = "2021-10-12"
GROUP BY `PLACE`
ORDER BY `SCORE` DESC, `id` ASC
LIMIT 3

So I could retrieve all three winners with no further processing. The results I'm trying to achieve should repeat neither the TEAM_ID nor PLACE, in this particular example it should output:

3   FRANCE  (Since it has the highest score in France at 137)
1   ITALY   (For the highest score in Italy at 74)
2   IRELAND (For the second-highest score in Ireland, since Team 1 already won in Italy)

The production model of this table has far more entries so it's unlikely there would be any clashes with too many second-places.

How can I achieve that?

Andaeriel
  • 21
  • 2
  • I kinda miss some context here. What would happen if a team in second place is the winner somewhere else? – SWeko Oct 11 '21 at 18:34
  • That would goes to the third place and on and on. The example has only a few teams, but it's unlikely it would have less than ten teams per map. – Andaeriel Oct 11 '21 at 18:37
  • 1
    TBH, this sounds like something that I'd handle procedurally, in code, and not in the database. There might be additional tie-breakers needed, i.e. if two second-place team score the same, or if we have the same number of points in two different games, etc... – SWeko Oct 11 '21 at 18:40
  • I was trying to avoid further coding since all data would be there at the moment of the output but I can't seem to find a solution that allows me a TEAM_ID + PLACE combination that would call either the place or the team only once, based on the amount of places registered. – Andaeriel Oct 11 '21 at 19:03
  • 1
    What if a team has the same score in two different locations, and both are otherwise candidates for a point? For example, what if team 1 scored 74 in both IE and IT, and team 5 scored 3rd in IT, rather than team 2? Going further, what if team 5 scored 31 in IT? – outis Oct 15 '21 at 07:41
  • Also, as part of a [minimal, complete sample](https://stackoverflow.com/help/mcve), please include [DDL](https://en.wikipedia.org/wiki/Data_definition_language) statements for sample tables and [DML](https://en.wikipedia.org/wiki/Data_manipulation_language) statements for sample input data (rather than a dump or ad hoc format, though you're already ahead of the curve by providing anything). Sample results don't need to be provided as code, as they aren't operated on by code. – outis Oct 15 '21 at 07:44

0 Answers0