php/mysql multiple order by

Question

I am working on a results table at the minute, I am wanting to sort a table, by points (highest at the top), if points are equal I want to sort by goal difference, and then if goal difference is equal I want to sort by goals scored.

So a table may look like this,

+--------+--------+----------+-----------------+--------+
|  Team  | Scored | Conceded | Goal Difference | Points |
+--------+--------+----------+-----------------+--------+
| Team A |     20 |       10 | +10             |     15 |
| Team B |     20 |       15 | +5              |     15 |
| Team C |     10 |       10 | 0               |      9 |
| Team D |      5 |       5  | 0               |      9 |
+--------+--------+----------+-----------------+--------+

So Team A wins the league because it has a better goal difference than Team B, Team C finish above Team D because they score more goals, and all other things are equal.

Is it possible to order this way in mysql, or will I need to parse the results with PHP?

No, you don't need PHP. There's more SQL than `SELECT * FROM tablename`. — Álvaro González, Aug 10 '16 at 09:11
Possible duplicate of [Order by more than one column with mysql](http://stackoverflow.com/questions/9889403/order-by-more-than-one-column-with-mysql) — jarvo69, Aug 10 '16 at 09:17

score 4 · Answer 1 · edited May 23 '17 at 12:06

4

Guess what, you can pass multiple column names to ORDER BY

SELECT * FROM mytable ORDER BY Points DESC, `Goal Difference` DESC, Scored DESC

You haven't given your table structure, but as pointed out by jpg if these fields are not numeric, ORDER by field_name + 0 may be more appropriate

edited May 23 '17 at 12:06

Community

1
1

answered Aug 10 '16 at 09:08

e4c5

52,766
11
101
134

1

You've forgot about Points ;) – nospor Aug 10 '16 at 09:09
In case that `Goal Difference` may be `char` or `varchar`, `\`Goal Difference\` + 0` will be better. :-D – Blank Aug 10 '16 at 09:13
Agreed @JPG but you know how these fastest gun of the west answers are shoot first edit later :) but there were a couple of others that needed shooting :) – e4c5 Aug 10 '16 at 09:14

score 1 · Answer 2 · answered Aug 10 '16 at 09:09

1

You can do like this using query

SELECT * from `your_table` ORDER BY points DESC, goal_difference DESC, scored DESC

answered Aug 10 '16 at 09:09

Haresh Vidja

8,340
3
25
42

score 0 · Answer 3 · answered Aug 10 '16 at 09:10

0

You can order by multiple columns at the same time.

SELECT some_cols
FROM table
WHERE (some conditions)
ORDER BY col1 DESC, col2, col3;

answered Aug 10 '16 at 09:10

Gulmuhammad Akbari

1,986
2
13
28

php/mysql multiple order by

3 Answers3

Linked

Related