CODEWITHSUNDEEP
mysqlsql
user2400545
user2400545

Reputation: 11

how can i retrieve the records from table using between operator in mysql

everybody

I want to retrieve the records from a table(price) based on the following query.My table is

+-----------+
| pprice    |
+-----------+
| 10 lakhs  |
| 11 lakhs  |
| 12 crores |
| 13 lakhs  |
| 15 crores |
| 16 lakhs  |
+-----------+

I'm using the following query

select * from price where pprice between '10 lakhs' and '20 lakhs';

but it returns the following result

+-----------+
| pprice    |
+-----------+
| 10 lakhs  |
| 11 lakhs  |
| 12 crores |
| 13 lakhs  |
| 15 crores |
| 16 lakhs  |
+-----------+

what's the problem in my query.please give me reply for this.

Upvotes: 1

Views: 520

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

If you're dealing with equal magnitudes, such as lakhs in your example, you can do something like this:

select *
  from price
 where pprice between '10 lakhs' and '20 lakhs'
   and pprice like '__ lakhs';

Upvotes: 1

Axel Amthor
Axel Amthor

Reputation: 11096

What happens is, that your mySql is not aware about lakhs and crores and splits off the number part at the first space while auto-converting the formats. And then: The result is quite proper!

Besides the design pattern here (measure as part of the data field, normalization, ...) what is the relation between lakhs and crores ? How should mysql know that 1 crore is larger than 1 lakhs (ore vice cersa, I don't what this is).

Upvotes: 0

PSR
PSR

Reputation: 40318

When we ares comparing integers, we are comparing their numerical values ;

i.e. 10 > 6.

When we are comparing strings, we are using an alphabetical comparison ;

i.e. '10'< '6'

SEE HERE

Upvotes: 0

Related Questions