SQL: Combine multiple columns into one for sorting (output only)

Question

I have a database like this:

|--------------------------------------------------------------------------|
| NAME               |  SCORE1  |  SCORE2  |  SCORE3  |  SCORE4  |  RATING |
|--------------------------------------------------------------------------|
| Joe Bloggs         |  -50     |  0       |  -10     |  -30     |  67     |
| Bob Bobbing        |  -30     |  -10     |  0       |  -10     |  74     |
| Marjorie Doors     |  0       |  -10     |  -30     |  -50     |  88     |
| etc... ------------------------------------------------------------------|

What I am trying to do is to find the highest-rated name for any given score position.

I do fine when there is only one score position possible:

SELECT name FROM db ORDER BY Score2 DESC, Rating DESC LIMIT 1

...gives me the highest-rated person with the best score for Score2.

---

What I now need is to find a way to combine two or more score columns (there are 23 in total) but still return the highest-rated person for any score combination given.

For example, if I wanted the highest-rated person for Score2 OR Score3, doing the above query gives me Joe Bloggs even though his rating is lower than Bob Bobbing's.

Similarly, if I wanted the highest-rated person for Score1 OR Score2 OR Score4, I'd still need to specify one of the scores to sort by first. I need a way to combine the results of all X columns specified, THEN sort by the combined score 'column', then by rating.

Solution 1

You may want to use the GREATEST() function:

With two or more arguments, returns the largest (maximum-valued) argument.

This code snippet does what you wanted for score2 and score3 columns in your example:

SELECT name, GREATEST( score2, score3 ) AS max_score, rating
FROM db
ORDER BY max_score DESC , rating DESC

Orders by combined scores, and if they are equal then orders by result (both highest to lowest).

For more columns, simply add them as arguments to GREATEST() function.

Solution 2

With the information from your comment I think what you're looking for is the GREATEST function.

Use it as follows:

SELECT GREATEST(Score1, Score2, Score3, ...) AS Score, Rating 
FROM db
ORDER BY 1, Rating 

ORDER BY 1 means order by the 1st column.

Comments

[Alma Do]

What does sum of columns has in common with relative ordering?

[Stuart Pinfold]

This was the solution. I wasn't previously aware of GREATEST - nice one, many thanks!

[amaster]

I must have misunderstood what the OP was asking for combine two or more score columns sounded to me like he wanted to sum the columns

[Mark Unsworth]

OP said they wanted to know highest score of Score1 OR Score2 OR Score4. Your solution works for the AND case.

[amaster]

See my comment above. This is really an old Post, but I think my error of misunderstanding the question still adds value as knowing what is not wanted.