2

I have the following MySQL Query

SELECT * FROM `travels`.`destinations` AS `Des`
WHERE `Des`.`name` LIKE '%act%' AND `Des`.`sold` = 'N' AND `Des`.`active` = '1'
GROUP BY `Des`.`name` ORDER BY CASE
WHEN `Des`.`name` REGEX 'act*' THEN 0
WHEN `Des`.`name` LIKE '%act' THEN 1
WHEN `Des`.`name` LIKE '%act%' THEN 2
ELSE 3 END, name LIMIT 10

What I am trying to achieve: actabc actzzz abcact zzzact abcactzzz act-act

When I use this group by mechanism, it is showing hyphenated result first, which it should. I want alphabets first, then symbols and then numbers. In the same order of wild cards.

These are working individually: How to sort MySQL results with letters first, symbols last? and This is sort of confusing (and doesn't give the group by as I want either): mysql regex get position of matched first alphabetic character

Any ideas?

Community
  • 1
  • 1
Keval Domadia
  • 4,768
  • 1
  • 37
  • 64

1 Answers1

0

Try this: 

SELECT * 
FROM travels.destinations AS D
WHERE D.name LIKE '%act%' AND D.sold = 'N' AND D.active = '1'
ORDER BY CASE WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE 'act%' THEN 0
              WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE '%act' THEN 1
              WHEN D.name REGEXP '^[a-zA-Z]*$' AND D.name LIKE '%act%' THEN 2
              ELSE 3 
         END, 
         D.name 
LIMIT 10
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83