Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sorting

mysql

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.

like image 598
Stuart Pinfold Avatar asked Jan 13 '14 13:01

Stuart Pinfold


People also ask

How do I combine data from multiple columns into one in SQL?

Select the same number of columns for each query. Corresponding columns must be the same general data type. Corresponding columns must all either allow null values or not allow null values. If you want to order the columns, specify a column number because the names of the columns you are merging are probably different.

How do I sort 3 columns in SQL?

Syntax: SELECT * FROM table_name ORDER BY column_name; For Multiple column order, add the name of the column by which you'd like to sort records first.

Can we sort multiple columns by ORDER BY clause in a single query?

You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.


2 Answers

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.

like image 114
user1853181 Avatar answered Nov 15 '22 07:11

user1853181


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.

like image 37
Rinze Smits Avatar answered Nov 15 '22 08:11

Rinze Smits