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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With