MySQL ORDER By one Specific value in column (having comma separated values)

Question

I want to sort the user record according to city (chosen from the drop-down list). like if I pass city_id 22 in my query then i want all the row first which are having city_ids 22 then the rest of the rows.

I know WHERE find_in_set('22',city_ids) will give me the correct result but it will not return the all rows so I want to achieve it using some ORDER BY .

I have tried ORDER BY FIND_IN_SET('22',city_ids) but its not working. How do I fix this, any best way?

User Table:

Id    Name     city_ids
1     AAAAA    10,22,30
2     BBBBB    11,28
3     CCCCC    15,22,44
4     DDDDD    19,99,
5     EEEEE    55,27,22

Want Sorted Output like below:

Id    Name     city_ids
1     AAAAA    10,22,30
3     CCCCC    15,22,44
5     EEEEE    55,27,22
2     BBBBB    11,28
4     DDDDD    19,99,

score 2 · Answer 1 · answered May 28 '19 at 14:12

You can do:

ORDER BY (FIND_IN_SET('22', city_ids) > 0) DESC

This puts matches first.

Then you should fix your data model. It is broken, broken, broken. Storing lists of ids in a string is wrong for many reasons:

The data types are (presumably) wrong. The ids are numbers and should not be stored as strings.
Storing multiple values in a column is not the SQL way to store things.
Ids should have properly declared foreign key relationships, which you cannot declare.
SQL does not have very good functions for processing strings.
The resulting queries cannot take advantage of indexes or partitioning, impeding performance.
SQL has this really great data structure for storing lists of things. It is called a table, not a string column.

So how to achieve it without Storing multiple values in a column, is there any best mysql solution for my problem. — amit gupta, May 28 '19 at 14:20
@amitgupta . . . You would use a junction/association table with one row per user and `city_id`. — Gordon Linoff, May 28 '19 at 14:55

score 1 · Accepted Answer · answered May 28 '19 at 14:39

The expression:

FIND_IN_SET('22', city_ids) > 0

will return 1 for all rows where '22' exists in column city_ids and 0 for the others.
So, after that you need add one more level for sorting by id ascending:

ORDER BY 
  FIND_IN_SET('22', city_ids) > 0 DESC, 
  id

See the demo.
Results:

| Id  | Name  | city_ids |
| --- | ----- | -------- |
| 1   | AAAAA | 10,22,30 |
| 3   | CCCCC | 15,22,44 |
| 5   | EEEEE | 55,27,22 |
| 2   | BBBBB | 11,28    |
| 4   | DDDDD | 19,99    |

MySQL ORDER By one Specific value in column (having comma separated values)

2 Answers2