1

I have a MySQL select statment and want to make this into one query.

 select * from users where...

The where clause will have four parts, and they need to be ordered this way:

 1. where description = "query"

 2. where description = "%query%"

 3. where description = "q1" or description = "q2"

 4. where description = "%q1%" or description = "%q2%"
cdub
  • 24,555
  • 57
  • 174
  • 303
  • Similar to this Question: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause Use FIELD as given in that link – Lucky Murari May 26 '11 at 06:27
  • http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field – Lucky Murari May 26 '11 at 06:39
  • is there no way to do where desc = "q1" as 1 or desc = "%q1%" as 2 and then do some kind of order on that – cdub May 26 '11 at 06:42

2 Answers2

2

One solution would be to UNION the results and add a fake OrderBy field for each query.

SELECT   1 as OrderBy, *
FROM     users
WHERE    description = "query"
UNION ALL
SELECT   2 as OrderBy, *
FROM     users
WHERE    description = "%query%"
...
ORDER BY
         OrderBy
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • okay this is more what i wanted, i'll test it now but it looks good to me! – cdub May 26 '11 at 06:52
  • Or just use 1 query and do `order by (description = 'query') DESC` – Johan May 26 '11 at 14:02
  • 1
    `SELECT * FROM users WHERE description = "query" or description = "%query%" or description IN ('q1','q2') or (description like '%q1%' or description like '%q2%') order by (description = "query") DESC, (description = "%query%") DESC, (description IN ('q1','q2')) DESC, (description like '%q1%' or description like '%q2%') DESC;` Not pretty, but it works. – Johan May 26 '11 at 14:51
0

I think the following will produce the results you need

select * from users 
where description like "%query%" 
  or description like "%q1%" 
  or description like "%q2%"
Johan
  • 74,508
  • 24
  • 191
  • 319
brett
  • 1
  • 3