I have a database with 3 tables. In table country I have id and name columns. The sport table also has id and name columns. Finally, the table match has id, player1, and player2(that are ids of country that play one against other), winner_id (id of country that won the match) and sport_id of the sport which was played.

I want to show the sport per country with the least wins. It should look like this:

How can I construct this query? I'm using SQL Server.

CodePudding user response：

You need to first cross-join the sports with the countries, then get the total.

Then you can use a row-number approach to get the bottom country in each sport

SELECT
  c.Country,
  c.Sport,
  c.Wins
FROM (
    SELECT
      c.name Country,
      s.name Sport,
      COUNT(m.winner_id) Wins,
      ROW_NUMBER() OVER (PARTITION BY s.sport_id, s.name ORDER BY COUNT(m.winner_id)) rn
    FROM country c
    CROSS JOIN sport s
    LEFT JOIN [match] m
      ON s.sport_id = m.sport_id AND m.winner_id = c.country_id
    GROUP BY
      s.sport_id,
      s.name,
      c.country_id,
      c.name
) c
WHERE c.rn = 1;

CodePudding user response：

I want to note that the used wording with least wins is not clear, in my solution with least wins means most matches played with least wins.

To get this ranking, we need to know how many matches a country has played in each sport and how many of those have been won.

SELECT 
    country.name AS country,
    sport.name AS sport,
    sport_wins.wins
FROM
    country
OUTER APPLY (
    SELECT TOP 1
        t.match_count,
        COALESCE(t.wins, 0) AS wins,
        t.sport_id
    FROM (    
        SELECT 
            COUNT(*) AS match_count,
            m_c.sport_id,
            t.wins
        FROM match m_c
        OUTER APPLY (
            SELECT 
                COUNT(*) AS wins,
                match.sport_id
            FROM match
            WHERE country.country_id = match.winner_id 
                  AND match.sport_id = m_c.sport_id
            GROUP BY match.sport_id
        ) t
        WHERE country.country_id IN (m_c.player1, m_c.player2)
        GROUP BY m_c.sport_id, t.wins
    ) t
    ORDER BY t.wins ASC, t.match_count DESC
) sport_wins
JOIN sport ON sport.sport_id = sport_wins.sport_id

Please, check a demo.