# Thread: Query help: Get the biggest win

1. Registered User
Join Date
Feb 2012
Posts
44

## Unanswered: Query help: Get the biggest win

I want to get the biggest win from a table. Two columns is needed here, homeScore and awayScore.

This is what I have come up with so far:

Code:
```SELECT *
FROM table
ORDER BY (homeScore-awayScore) DESC, awayScore DESC LIMIT 1```
Of course, this will only get the biggest home win. How could I include the calculation of the biggest away win as well?

Thanks for any kind of help and sorry if it's a stupid question.
Last edited by Haydn; 02-20-12 at 05:35. Reason: Forgot the limit

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
ORDER BY GREATEST(homeScore-awayScore,awayScore-homeScore) DESC

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Another alternative (although I do love Rudy's solution!)
Code:
```ORDER
BY Abs(homescore - awayscore) DESC```
MySQL :: MySQL 5.0 Reference Manual :: 11.6.2 Mathematical Functions

4. Registered User
Join Date
Feb 2012
Posts
44
Originally Posted by r937
ORDER BY GREATEST(homeScore-awayScore,awayScore-homeScore) DESC
Thanks a lot, appreciate it.

Edit: I didn't notice your post there gvee, thanks for that one as well!

Although I have one extra question here. If the difference of two results is the same, but the winning result have more goals, I would like the latter to be sorted first.

Example:
6-1
5-0

The 6-1 result should be sorted before the 5-0....is that a problem?
Last edited by Haydn; 02-20-12 at 06:59.

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Essentially you want the game with the highest total score first:
Code:
```ORDER
BY Abs(homescore - awayscore) DESC
, homescore + awayscore DESC```

6. Registered User
Join Date
Feb 2012
Posts
44
Originally Posted by gvee
Essentially you want the game with the highest total score first:
Code:
```ORDER
BY Abs(homescore - awayscore) DESC
, homescore + awayscore DESC```
Of course, thanks!

Your variant with the Abs was clever indeed.

I think I'm falling in love with this website before 24 hours has passed since I registered.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•