CODEWITHSUNDEEP
mysqlsql
warmturtle987
warmturtle987

Reputation: 13

Sorting by difference in column values

I have the following columns in my sql database table cities:

zipcode | totalPetPopulation | numCats | numDogs

My goal is to get all rows where numCats > numDogs but also I need to order them in descending order based on the difference between numCats and numDogs. That is, if I have the following table:

zipcode | totalPetPopulation | numCats | numDogs 
44412   | 45                 | 10      | 35
15442   | 80                 | 50      | 30
44133   | 90                 | 70      | 20
90293   | 11                 | 5       | 6
11123   | 25                 | 20      | 5

I need the result to be:

44133 | 90 | 70 | 20 (difference is 70 - 20 = 50)
15442 | 80 | 50 | 30 (difference is 50 - 30 = 20)
11123 | 25 | 20 | 5  (difference is 20 - 5 = 15)

I have this so far for my query:

SELECT * FROM cities WHERE numCats > numDogs;

Upvotes: 1

Views: 164

Answers (1)

Nick
Nick

Reputation: 147206

You can just put the difference expression in your ORDER BY clause:

SELECT *
FROM cities
WHERE numCats > numDogs
ORDER BY (numCats - numDogs) DESC

Output:

zipcode     totalPetPopulation  numCats     numDogs
44133       90                  70          20
15442       80                  50          30
11123       25                  20          5

Demo on dbfiddle

Upvotes: 3

Related Questions