1

I want to use a function that returns for me the same result in one line is that's possible??

MIN(
    CASE 
       WHEN (data.pro LIKE '1 %'   )  THEN 1
       WHEN (data.pro LIKE '2 %'   )  THEN 2
       WHEN (data.pro LIKE '3 %'   )  THEN 3
       WHEN (data.pro LIKE '4 %'   )  THEN 4
       WHEN (data.pro LIKE '5 %'   )  THEN 5
       WHEN (data.pro LIKE '6 %'   )  THEN 6
    END) as products

Many thanks for any help.

2 Answers 2

3

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string:

data.pro rlike '^([1-6] )'

Now put this in a CASE:

case when data.pro rlike '^([1-6] )' 
     then cast(substring(data.pro from 1 for 1) as unsigned)
end

See the result in MySQL 5.5 on SQLFiddle.

0
1

If you have an index on data.pro:

select substring(min(data.pro),1,1) from products where substring(data.pro,1,2) in ('1 ','2 ','3 ','4 ','5 ','6 ') order by data.pro;

Instead of a select case on each record of your products, take the minimum-record between for your range from 1-6 (starts with that number anyway). If there is a good index for that, only 1 record will be examined and received.

A good practice is to test statements with 'explain ...', to see how many records get examined.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.