PostgresQL: Finding records that do not exist in query

Question

Below, I have a query for a rankings table of players in a leaderboard.

Player information (including pseudonym) is stored in the player table, while rankings for each "matchday" (identified by edition_id) are stored in players_rankings as the competition is lineal (there's no points system, so rankings can't be computed mathematically). Information for each fixture is stored in set (sw denotes set wins, while sl denotes set losses).

SELECT
  players_rankings.rank, players_rankings.change, player.pseudonym,
  SUM(tot.sw) AS sw,
  SUM(tot.sl) AS sl
FROM players_rankings, player, (
    SELECT
      player1_id AS player_id,
      CASE
        WHEN score1 > score2 THEN 1 ELSE 0
      END AS sw,
      CASE
        WHEN score1 < score2 THEN 1 ELSE 0
      END AS sl
    FROM set WHERE edition_id = 1
    UNION ALL
    SELECT
      player2_id,
      CASE
        WHEN score1 < score2 THEN 1 ELSE 0
      END,
      CASE
        WHEN score1 > score2 THEN 1 ELSE 0
      END
    FROM set WHERE edition_id = 1
  ) AS tot
WHERE
  players_rankings.edition_id = 1 AND
  tot.player_id = players_rankings.player_id AND
  players_rankings.player_id = player.id
GROUP BY 1, 2, 3
UNION
SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
FROM players_rankings, player
WHERE
  players_rankings.edition_id = 1 AND
  players_rankings.player_id = player.id
ORDER BY 1;

Which produces the following result:

-----+--------+---------------+----+----+
rank | change | pseudonym     | sw | sl |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 1  | 0  |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 0  | 0  |
-----+--------+---------------+----+----+
2    | 0      | Player Two    | 0  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 1  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 0  | 0  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 1  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 0  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 1  | 0  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 0  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 0  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 1  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 1  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 0  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 1  |
-----+--------+---------------+----+----+
9    | -1     | Player Nine   | 0  | 0  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 1  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 0  |
-----+--------+---------------+----+----+
11   | 0      | Player Eleven | 0  | 0  |
-----+--------+---------------+----+----+
12   | 0      | Player Twelve | 0  | 0  |
-----+--------+---------------+----+----+

My goal with the query after the UNION was to get only registered players that didn't feature in the first "matchday" (players_rankings.edition_id = 1, i.e. players Two, Nine, Eleven, and Twelve), but I hit a brick wall trying different methods to achieve that, including different JOINs. As such, I went back to the drawing board and used the aforementioned query to start again with the duplicate values as shown above. Below is the desired result:

-----+--------+---------------+----+----+
rank | change | pseudonym     | sw | sl |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 1  | 0  |
-----+--------+---------------+----+----+
2    | 0      | Player Two    | 0  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 1  | 0  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 1  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 1  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 1  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 1  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 1  |
-----+--------+---------------+----+----+
9    | -1     | Player Nine   | 0  | 0  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 1  |
-----+--------+---------------+----+----+
11   | 0      | Player Eleven | 0  | 0  |
-----+--------+---------------+----+----+
12   | 0      | Player Twelve | 0  | 0  |
-----+--------+---------------+----+----+

How should I go about achieving this?

If you increase the number of suqueries by one level and use the condition sw = 1 or sl = 1, the desired output will be reached. — persian-theme
– persian-theme, Commented Jan 5, 2022 at 13:07

persian-theme · Accepted Answer · 2022-01-05 14:13:02Z

1

Use window function ROW_NUMBER() and partiton by rank and sort with case statement.

Using the Row_Number function, group the rows that have the same rank, and then, based on the fact that the row has the condition sw = 1 OR sl = 1, the value of one is included in the sort, otherwise the value 0 is then sorted in descending order.

In fact, the Row_Number function numbers the rows based on the same rank, and in the main query, the rows that are numbered number one are fetched.

SELECT rank,change,pseudonym,sw,sl
FROM
  (SELECT *,
       ROW_NUMBER() OVER(PARTITION BY rank ORDER BY CASE WHEN sw = 1 OR sl = 1 THEN 1 ELSE 0 END DESC)   AS num
  FROM
    (SELECT
      players_rankings.rank, players_rankings.change, player.pseudonym,
      SUM(tot.sw) AS sw,
      SUM(tot.sl) AS sl
    FROM players_rankings, player, (
        SELECT
          player1_id AS player_id,
          CASE
            WHEN score1 > score2 THEN 1 ELSE 0
          END AS sw,
          CASE
            WHEN score1 < score2 THEN 1 ELSE 0
          END AS sl
        FROM set WHERE edition_id = 1
        UNION ALL
        SELECT
          player2_id,
          CASE
            WHEN score1 < score2 THEN 1 ELSE 0
          END,
          CASE
            WHEN score1 > score2 THEN 1 ELSE 0
          END
        FROM set WHERE edition_id = 1
      ) AS tot
    WHERE
      players_rankings.edition_id = 1 AND
      tot.player_id = players_rankings.player_id AND
      players_rankings.player_id = player.id
    GROUP BY 1, 2, 3
    UNION
    SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
    FROM players_rankings, player
    WHERE
      players_rankings.edition_id = 1 AND
      players_rankings.player_id = player.id) T) T
WHERE num = 1      
ORDER BY 1;

Demo in db<>fiddle

edited Jan 5, 2022 at 14:13

answered Jan 5, 2022 at 13:30

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Davy Kamanzi Over a year ago

This worked. Thanks! Could you explain what's happening with the functions step-by-step? (Aside from the queries I mentioned earlier that you've used in your answer, of course)

persian-theme Over a year ago

i edited answer . see this link dbfiddle.uk/…

Davy Kamanzi Over a year ago

Thanks for the explanation! Had to play around with the code in bits to really grasp the concept but I've understood it now. Much appreciated!

gsiems · Accepted Answer · 2022-01-05 14:04:11Z

Caveat: I haven't finished my morning coffee yet... If I understand your question correctly the following (un-tested) approach could work:

WITH pr AS (
    SELECT players_rankings.player_id,
            players_rankings.rank,
            players_rankings.change,
            player.pseudonym
        FROM players_rankings
        JOIN player
            ON ( players_rankings.player_id = player.id )
        WHERE players_rankings.edition_id = 1
),
tot AS (
    SELECT t.player_id,
            sum ( t.sw ) AS sw,
            sum ( t.sl ) AS sl
        FROM (
            SELECT player1_id AS player_id,
                    CASE
                        WHEN score1 > score2 THEN 1
                        ELSE 0
                        END AS sw,
                    CASE
                        WHEN score1 < score2 THEN 1
                        ELSE 0
                        END AS sl
                FROM SET
                WHERE edition_id = 1
            UNION ALL
            SELECT player2_id,
                    CASE
                        WHEN score1 < score2 THEN 1
                        ELSE 0
                        END,
                    CASE
                        WHEN score1 > score2 THEN 1
                        ELSE 0
                        END
                FROM SET
                WHERE edition_id = 1
            ) AS t
        GROUP BY t.player_id
)
SELECT pr.rank,
        pr.change,
        pr.pseudonym,
        0 AS sw,
        0 AS sl
    FROM pr
    FULL OUTER JOIN tot
        ON ( pr.player_id = tot.player_id )
    WHERE tot.pseudonym IS NULL
    ORDER BY 1 ;

edit fix columns in full outer join

Collectives™ on Stack Overflow

PostgresQL: Finding records that do not exist in query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related