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
– Álvaro González, Commented Aug 10, 2016 at 9:11
Possible duplicate of Order by more than one column with mysql — jarvo69
– jarvo69, Commented Aug 10, 2016 at 9:17

Community · Accepted Answer · 2017-05-23 12:06:54Z

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, 2017 at 12:06

CommunityBot

11 silver badge

answered Aug 10, 2016 at 9:08

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nospor Over a year ago

You've forgot about Points ;)

Blank Over a year ago

In case that Goal Difference may be char or varchar, `Goal Difference` + 0 will be better. :-D

e4c5 Over a year ago

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 :)

Haresh Vidja · Accepted Answer · 2016-08-10 09:09:54Z

1

You can do like this using query

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

answered Aug 10, 2016 at 9:09

Haresh Vidja

8,4713 gold badges29 silver badges44 bronze badges

Comments

Gulmuhammad Akbari · Accepted Answer · 2016-08-10 09:10:55Z

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, 2016 at 9:10

Gulmuhammad Akbari

2,0142 gold badges15 silver badges30 bronze badges

Collectives™ on Stack Overflow

php/mysql multiple order by

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related