The matches table contains data for cricket matches between different teams and the winner.

Write a query to filter out redundant records from the table (e.g. row 1 and row 2 refers to the same match, so we need to keep only 1 of these rows in resultant table)

How to solve it assuming that the data is not limited to just above values, lets say there are 10 teams. I tried to solve using self join but the team order is also not consistent like RCB VS RR is same in both cases and for others it is reversed. I am just a beginner with SQL

CodePudding user response：

We need to unpivot the columns to have both teams in the same column and we need to add row_number() to make sure we're keeping the matches correctly. Then we pivot it back using group by, but this time we have the duplicates in the correct columns. Then we select distinct to get the results we were looking for. It would be simpler if you had game_id or game_date or something like that.

with t2 as (
           select Team_1 as team, Winner, row_number() over(order by Team_1) as match_num from   t

           union all

           select Team_2, Winner, row_number() over(order by Team_1) from   t
           )

select     distinct Team_1, Team_2, Winner
from      (
           select   match_num
                   ,max(team)   as Team_1
                   ,min(team)   as Team_2
                   ,max(Winner) as Winner
           from     t2
           group by match_num
          ) t2

Fiddle