ORDER BY CASE

[chiprivers]

I have got the following SELECT query which is returning false:

SELECT 
r.indicator 
FROM 
resources AS r 
WHERE 
r.stationsREF = 1 
AND 
r.record_status = 1
ORDER BY
CASE r.indicator WHEN 'P' THEN 1 WHEN 'A' THEN 2 WHEN 'R' THEN 3 WHEN 'M' THEN 4 WHEN 'C' THEN 5 ELSE 6

I know that it is the ORDER BY CASE syntax which is causing the problem because it works fine when I remove this part of the query, however the same syntax works fine on another similar query that I am using.

 

Can anybody tell me what I need to change?

 

If it is not obvious, I need to order by the r.indicator column where 'P' is listed first, 'A' second, 'R' third, 'M' fourth and anything else afterwards.

[kickstart]

Hi

 

Think you are missing an END at the end of the case statement.

 

All the best

 

Keith

[chiprivers]

Well spotted Keith, such a silly mistake! It is at least now beign accepted as a valid query.  However, as an extension to this question, I need to amend it so that instead of the values in the r.indicator column being equal to P, A, R, M or C, I need to see if the values contain these letters.  I thought I could do this by just changing the 'P' to a '%P%' but this is not working.

 

Any suggestions?

[kickstart]

Hi

 

Totally untested, but something like this might do it

 

SELECT 	r.indicator 
FROM resources AS r 
WHERE r.stationsREF = 1 
AND r.record_status = 1
ORDER BY
CASE TRUE WHEN r.indicator LIKE '%P%' THEN 1 WHEN r.indicator LIKE '%A%' THEN 2 WHEN r.indicator LIKE '%R%' THEN 3 WHEN r.indicator LIKE '%M%' THEN 4 WHEN r.indicator LIKE '%C%' THEN 5 ELSE 6 END

 

However if that works I suspect it might be quite slow.

 

All the best

 

Keith

[chiprivers]

Thanks Keith.  I has just come up with the same myself, just without the 'TRUE' bit, and it is working perfectly.