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;
Advertisement
Answer
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