'How to Order by a specific column name in my SQL

I'm on a work of a social network site, so I want to list the users based on logged in user location

My table sample table structure is (id, name, username, password, country, state)

Id, name, username, password, country, state

1, rameez, rameezrami, password, india, kerala

2, rameez1, rameezrami, password, india, kerala

3, rameez2, rameezrami, password, country1, state1

4, rameez3, rameezrami, password, country2, state2

If logged in user is from kerala I want to list all kerala users first then all other state users from india, then from other country how can I do this?

mysql


Solution 1:[1]

I think this will help you.

SELECT * FROM tableName WHERE (state='kerala' OR country='India' OR 1=1) ORDER BY state='kerala' DESC, country='India' DESC

Solution 2:[2]

Since you seem to want to order by some non-standard way, you have to create a column with your criteria and use that in your ORDER BY

   SELECT id, name, username, password, country, state, 
           if(state = 'kerala', 0, if(country='india', 1,2)) as ord_col
   FROM your_table
   ORDER BY ord_col

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tushar Bhaware
Solution 2 Dan

Related Questions