3

I have this query:

SELECT * FROM table WHERE key LIKE '1,2,3,%' OR key LIKE '1,2,%' OR key LIKE '1,%'

Is it posible to sort records returned from this query based on which conditions was matched first. I'd like to get all records that match key LIKE '1,2,3,%' first, then key LIKE '1,2,%' and the others after.

For example, if I have these records:

key: "1,2,3,4"
key: "1,2,5"
key: "1,4"
key: "1,2,5,6"
key: "1,3"
key: "1,2,3,4,7"
key: "1,2,4"

I would like them to be sorted like so:

key: "1,2,3,4"
key: "1,2,3,4,7"
key: "1,2,4"
key: "1,2,5"
key: "1,2,5,6"
key: "1,3"
key: "1,4"

Is it possible to do?

remi
  • 1,293
  • 8
  • 21
  • Well, what have you tried? Notice it's just normal lexical string sorting... –  Sep 16 '10 at 19:37
  • Your query is redundant. using just "WHERE key LIKE '1,%'" will give you the same results. – Kevin Crowell Sep 16 '10 at 19:37
  • @Kevin, yes you are absolutely right. Since I'm now using the more specific conditions in the "ORDER BY CASE", I've now changed my WHERE condition to only "1,%". – remi Sep 18 '10 at 12:48

3 Answers3

3

Use MATCH ... AGAINST and order by rank. It exactly does what you want.

shamittomar
  • 46,210
  • 12
  • 74
  • 78
2

.... ORDER BY CASE
WHEN key LIKE '1,2,3,%' THEN 1
WHEN key LIKE '1,2,%' THEN 2
ELSE 3
END

a1ex07
  • 36,826
  • 12
  • 90
  • 103
1

Does using "UNION" could do the job?

SELECT * FROM table WHERE key LIKE '1,2,3,%' UNION SELECT * FROM table WHERE key LIKE '1,2,%' UNION SELECT * FROM table WHERE key LIKE key LIKE '1,%'

Pascal Bois
  • 11
  • 1