CODEWITHSUNDEEP
mysqlsql
nTuply
nTuply

Reputation: 1364

MySQL ORDER BY 2 Columns with condition

I have the following table, let's call it buildings.

building_code  |  city  |  phone
1              |  A     |  +1 212 444 555
2              |  B     |  +2 333 444 555
3              |  C     |  +3 222 234 122
4              |  A     |  +1 212 555 444

So each city has a few buildings. What I'm trying to do now is select the city and phone number from the buildings. However, I want to sort city by Ascending and also if a city has multiples buildings (example A), then I also want to sort the phone number by Descending order.

The end-result would be

city  |  phone
A     |  +1 212 555 444
A     |  +1 212 444 555
B     |  +2 333 444 555
C     |  +3 222 234 122

So the query I tried is SELECT city, phone FROM buildings ORDER BY city ASC This works and sorts city name in Ascending order. However, I am not sure how to order the phone in descending order.

I tried SELECT city, phone FROM buildings ORDER BY city ASC, IF(COUNT(city) > 1, phone, '') DESC; and it gave me an error.

Upvotes: 1

Views: 438

Answers (2)

Anurag
Anurag

Reputation: 307

Try this

SELECT city, phone FROM buildings ORDER BY city ASC, phone DESC;

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

SELECT city, phone FROM buildings ORDER BY city ASC, phone DESC

as simple as that. See it working live here

Upvotes: 3

Related Questions