CODEWITHSUNDEEP
mysqlsql
Sahil
Sahil

Reputation: 9478

Query to return winner and loser in the cups table?

I have a table which has this schema

year,team1,team2,winner

2010,12,13,12

I want to return data in this form

year,winner,runner-up

Can it be done using a sql query?

Upvotes: 1

Views: 279

Answers (4)

Ullas
Ullas

Reputation: 11556

Just using a CASE expression.

Already we know the winning team from the winner column. so the runner-up would be the other one. So we can use a case statement to return the team which not in the winner column..

Query

SELECT `year`, winner,
    CASE winner WHEN team1 THEN team2 ELSE team1 END AS `runner-up`
FROM tblMatches;

SQL Fiddle

Upvotes: 0

Gaurav Lad
Gaurav Lad

Reputation: 1808

Create a VIEW with SELECT, it will act as temp table form you and will return that table with required details.
CREATE VIEW GET_WINNER as SELECT year, winner, ABS(winner - team1 - team2) AS runner up FROM YourTable

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

thinking out the box

winner - winning team_id = 0
0 - the not winning team_id = -team_id

SELECT year, winner, ABS(winner - team1 - team2) AS `runner up`
FROM YourTable

SQLFiddle

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

Try using the following query. The meat-and-potatoes is the CASE statement, which, for each record in your original table, will choose the team which is not the winner as the runner-up.

SELECT year, winner, CASE WHEN team1 = winner THEN team2 ELSE team1 END AS runnerUp
FROM teamStats

Click the link below for a running example:

SQLFiddle

Upvotes: 1

Related Questions