Re: BETWEEN condition is not working...

[Phillip Ward]

Say your line is made up of these stations:

Y, T, R, E, W, Q

The BETWEEN operator treats this list as:

E, Q, R, T, W, Y

So, because your stations are not arranged in alphabetical order, you have to add a "sequence" column of your own to specify that order.

select * from stations 

+---------+-----+
| station | seq | 
+---------+-----+
| Y       |   0 |
| T       |   1 |
| R       |   2 |
| E       |   3 |
| W       |   4 |
| Q       |   5 |
+---------+-----+

Now you can say "find me stations between the first and last" - based on your new sequence column: 

select * 
from stations 
where seq between 2 and 4  
order by seq ; 

+---------+-----+
| station | seq | 
+---------+-----+
| R       |   2 |
| E       |   3 |
| W       |   4 |
+---------+-----+



Of course, your user's aren't going to know what these sequence numbers are, nor should they, so you need to do the translation for them: 





select * 
from stations 
where seq 
between ( select seq from stations where station = 'Y' ) 
and ( select seq from stations where station = 'Q' )
order by seq ; 

+---------+-----+
| station | seq | 
+---------+-----+
| Y       |   0 |
| T       |   1 |
| R       |   2 |
| E       |   3 |
| W       |   4 |
| Q       |   5 |
+---------+-----+



Note: you still have to specify the arguments to BETWEEN in the right (ascending) order!  





Regards,   Phill  W.