Get CASE NUMBER printed for WHEN ORDER BY Clause in MYSQL-CodePudding

Is there a way to know (or at least print) which WHEN CASE got executed for which result from MYSQL query?

for e.g., I have the following SQL which gives a priority order for the ORDER by clause.

select col1,col2 from tablename
WHERE col1 like '%var1%' and col2<>'0'
ORDER BY
  CASE
    WHEN col1 = 'somevalue' THEN 0
    WHEN col1 LIKE '%,somevalue,%' THEN 1
    WHEN col2 LIKE '%,somevalue,%' THEN 2
    WHEN col2 LIKE '%somevalue%' THEN 3
    ELSE 4
  END
limit 500

The result comes up, but for each row returned as result, I want to also print the CASE NUMBER (0,1,2,3,4) which got matched.

Is it possible?

CodePudding user response：

Use the CASE expression in the SELECT list:

SELECT col1, col2,
       CASE
         WHEN col1 = 'somevalue' THEN 0
         WHEN col1 LIKE '%,somevalue,%' THEN 1
         WHEN col2 LIKE '%,somevalue,%' THEN 2
         WHEN col2 LIKE '%somevalue%' THEN 3
         ELSE 4
       END AS result
FROM tablename
WHERE col1 like '%var1%' and col2<>'0'
ORDER BY result
LIMIT 500;