How to MySQL Between used for Higher value to Lower value?

Question

Here I mentioned query:

lowerValue = 3, upperValue = 6

SELECT * FROM SampleTable where tableDay BETWEEN 'lowerValue' AND 'upperValue';

This query is working fine, but I need given below query type

lowerValue = 3, upperValue = 6

SELECT * FROM SampleTable where tableDay BETWEEN 'upperValue' AND 'lowerValue';

It's return as empty list, because both values are dynamic, what I'm doing wrong?

Answer 1

As a quickfix you could go with GREATEST() / LEAST() comparison functions:

SELECT *
FROM   SampleTable
WHERE  tableDay BETWEEN LEAST(upperValue, lowerValue) AND
                        GREATEST(upperValue, lowerValue);

but if upperValue can be lesser than lowerValue then use different names.

Answer 2

Your first argument must always be the lower one.

From documentation of SQL:

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.