Sort SQL records based on agreed conditions

Question

I have this query:

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

Is it possible to sort the records returned from this query based on what conditions were matched first. First, I would like to get all the records that match key LIKE '1,2,3,%', then the key LIKE '1,2,%'others after.

For example, if I have these entries:

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 this:

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"

Can this be done?

Answer 1

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

Answer 2

Use MATCH ... AGAINSTand sort by rank. He definitely does what you want.

Answer 3

"UNION" ?

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,%'