2

I'm trying to achieve a result where only one result for each TEAM and each PLACE is returned. The twist is that the highest result should from each place should have priority. My table currently looks something 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 people playing in all of them at the same time.

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.

I've tried over 30 queries I've found in several correlated questions, but none of them seems to be applicable to my situation.

Basically: "Return the highest score on each PLACE, but only calls each TEAM once. If that certain TEAM was already called, ignore it, get the second place."

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
  • The solution is server dependent. Here is similar discussion for Microsoft SQL Server 2005 https://stackoverflow.com/questions/1954198/sql-query-to-return-only-1-record-per-group-id – 30thh Oct 12 '21 at 03:55
  • Here is one more thread with examples https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – 30thh Oct 12 '21 at 03:58
  • Thank you for the link, I will take a look, I'm running a MariaDB on CentOS. – Andaeriel Oct 12 '21 at 03:59
  • 1
    What I am missing in those examples are the comparison between the values, in the case of eliminating the highest value if TEAM was already called once, but I'm looking further. – Andaeriel Oct 12 '21 at 04:05
  • I don't think the requirement "but only calls each TEAM once" can be fulfilled using SQL statement of realistic size. – 30thh Oct 12 '21 at 04:07
  • IMO you should write a stored procedure for this, using a loop and save a blacklist of TEAM_ID**s** after you get each place-TEAM_ID-score in your result – Pham X. Bach Oct 12 '21 at 04:10
  • Gotcha, If that's the case, I will try to move in a coded post-processing (where unfortunately I will be limited to one-dimensional arrays...). – Andaeriel Oct 12 '21 at 04:16

0 Answers0