CODEWITHSUNDEEP
sql
Aishwarya Deokuliar
Aishwarya Deokuliar

Reputation: 1

SQL to display name of players who played maximum matches together

Display the name of players who played maximum matches together. Here Rafal and Nadal should be displayed.

enter image description here

Upvotes: 0

Views: 252

Answers (2)

James
James

Reputation: 3015

You don't specify the db engine you are using, but for example in sql server you could do it like this:

select top 1
  case when winner > runner then winner else runner end,
  case when winner < runner then winner else runner end,
  count(*) c
from matches
group by 
  case when winner > runner then winner else runner end,
  case when winner < runner then winner else runner end
order by c desc

You can test on this db<>fiddle

The case when statements order the names always in the same way. Then, you count the number of times that match happens, and then you order by the count, getting just the first top record.

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

here is one way:

select winner player1, runner player2 from (
select year, winner, runner
from table
union all 
select year, runner, winner
from table
) t 
group by winner, runner 
order by count(distinct year) desc 
limit 1

Upvotes: 0

Related Questions