Finding top scores over multiple columns

If my DATA samp开发者_运维技巧le is

team name  score1  score2
Adele's     15      18
Madonna   16       3
Britanny    9        12

How can I get a top 5 list of scores using both columns - my output should be

Adele's   18
Madonna   16
Adele's   15
Britanny  12

---

Derek Kromm's answer needs a slight modification to avoid a bug when a team has two identical scores in the top 5 (e.g. same as the example, except Madonna score1 and score2 are both 16). The default behavior of union is to remove duplicate rows, so only one of the scores would be kept. Based on the question, I don't think this is the desired behavior. Addition of the 'all' keyword to union will prevent this bug.

select * from (
  select team, score1 from tbl
  union all select team, score2 from tbl) a
order by score1 desc
limit 5;

I would have posted this as a comment to Derek Kromm's answer, except that I don't have enough reputation to do so. Sorry if posting it as an answer is not the correct etiquette.

---

SELECT "team name" AS team_name, GREATEST(score1, score2) AS max_score FROM users ORDER BY max_score DESC LIMIT 5

Purely as a side note, if you can, it's worth renaming the "team name" column to team_name in your database, as it makes things much easier when querying and working with the data :)

---

You can use a UNION to get a list of all teams and scores. Then, use ORDER and LIMIT to get the top 5.

select * from (
  select team, score1 from tbl
  union select team, score2 from tbl) a
order by score1 desc
limit 5;