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.

23 columns? I think it's better to add new table with score number and score value. Also _"highest-rated person for Score1 OR Score2 OR Score4"_ is hard to understand precisely. Could you add some samples of desired results? — Alma Do, Jan 13 '14 at 13:54
It is unclear what you mean by "combining" columns. In your "Score2 OR Score3" example, it looks like Joe and Bob would have the same score, save for Bob's higher rating. Does combine mean "add"? (Thus, you SUM() the relevant fields?) Could you simply sort by "Rating" first? — poundifdef, Jan 13 '14 at 13:57
@poundifdef Exactly, Joe and Bob both have a zero in Score2 and Score3, so the selection goes on rating. If I sorted by rating first, Marjorie would be top because her rating is higher than anyone else's. I need to take into account the scores first, then the rating. If there is no-one with zero for the specified score column(s), then it must then consider those with -10 and choose the highest-rated person from that pool instead. — Stuart Pinfold, Jan 13 '14 at 14:03

score 11 · Accepted Answer · answered Jan 13 '14 at 14:10

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.

This was the solution. I wasn't previously aware of GREATEST - nice one, many thanks! — Stuart Pinfold, Jan 13 '14 at 16:01

score 1 · Answer 2 · edited Jan 13 '14 at 14:14

1

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.

edited Jan 13 '14 at 14:14

miltos

1,009
1
6
10

answered Jan 13 '14 at 14:10

Rinze Smits

810
6
5

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

See this reference answer on Stack Overflow.

You can add columns together using (column1 + column2)

For Instance:

SELECT name FROM db ORDER BY (SCORE1 + SCORE2 + SCORE3) DESC, Rating DESC LIMIT 1

edited May 23 '17 at 12:17

Community

1
1

answered Jan 13 '14 at 13:56

amaster

1,915
5
25
51

What does sum of columns has in common with relative ordering? – Alma Do Jan 13 '14 at 13:57
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 – amaster Jan 13 '14 at 18:27
OP said they wanted to know highest score of Score1 OR Score2 OR Score4. Your solution works for the AND case. – Mark Unsworth Dec 06 '19 at 10:35
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. – amaster Dec 06 '19 at 12:44

score -1 · Answer 4 · answered Nov 20 '19 at 05:25

If the data in the columns are strings, then using IF() in the ORDER BY could work. For example, if you have two columns (e.g., Name_First and Name_Last) and you want to order by Name_First if Name_Last is NULL and order by Name_Last if it is not NULL, then:

SELECT * FROM MyTable ORDER BY IF(Name_Last IS NULL,Name_First,Name_Last);

Conceptually, it is IF(expression1, expression2, expression3): if expression1 is true, then it returns expression2, otherwise it returns expression3.

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

4 Answers4