CODEWITHSUNDEEP
mysqlsqlsql-order-byfind-in-set
amit gupta
amit gupta

Reputation: 1330

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

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,

Upvotes: 0

Views: 880

Answers (2)

forpas
forpas

Reputation: 164099

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    |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions