CODEWITHSUNDEEP
mysqlsqlsorting
Stuart Pinfold
Stuart Pinfold

Reputation: 318

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

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.

Upvotes: 6

Views: 19387

Answers (4)

Arya
Arya

Reputation: 734

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.

Upvotes: -2

amaster
amaster

Reputation: 2163

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

Upvotes: 0

Rinze Smits
Rinze Smits

Reputation: 868

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.

Upvotes: 1

user1853181
user1853181

Reputation: 785

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.

Upvotes: 11

Related Questions