Filtering numbers using MySQL

Question

I am running the following MySQL query to filter the number of listings with prices within a certain range.

SELECT `listing_id`, `price` FROM (`listings`) 
WHERE `post_timestamp` BETWEEN (NOW() - 0 AND NOW() - 5) 
AND `price` > '0' AND `price` < '10000'
ORDER BY `post_timestamp` desc

Problem: This does not give me any results although there are rows where the price column has values between 0 and 10,000.

Now the following SQL query returns the correct results

SELECT `listing_id`, `price` FROM (`listings`) 
WHERE `post_timestamp` BETWEEN (NOW() - 0 AND NOW() - 5) 
AND `price` < '10000'
ORDER BY `post_timestamp` desc

But this query below returns no results!!

SELECT `listing_id`, `price` FROM (`listings`) 
WHERE `post_timestamp` BETWEEN (NOW() - 0 AND NOW() - 5) 
AND `price` > '0'
ORDER BY `post_timestamp` desc

This is really confusing me, wonder if anyone have an explanation/solution for this?

Edit The following code gives me results!

SELECT `listing_id`, `price` FROM (`listings`) 
WHERE `price` > '0'

Field type: int(8)

Removing quotes around numerical values do not make a difference

What is the field type of price?

Pekka
– Pekka

2011-12-10 17:15:56 +00:00
Commented Dec 10, 2011 at 17:15 — Pekka
– Pekka, Commented Dec 10, 2011 at 17:15

ajreal · Accepted Answer · 2011-12-10 17:51:45Z

4

(I found myself don't understand what is this explanation),
so, in short

Remove the parenthesis to :-

WHERE `post_timestamp` BETWEEN NOW() - 0 AND NOW() - 5 //first where clause
AND `price` < '10000'

It might return zero matches,
as the comparison is too short (now() - 5 seconds)

To check for last 5 days

WHERE `post_timestamp` BETWEEN DATE_SUB( NOW(), INTERVAL 5 DAY) AND NOW()
AND `price` < 10000

edited Dec 10, 2011 at 17:51

answered Dec 10, 2011 at 17:26

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

john personna Over a year ago

Yes, the manual says "BETWEEN min AND max" simply expands to "(min <= expr AND expr <= max)"

Nyxynyx Over a year ago

If the -5 is in seconds, why did the 2nd query return the correct result? (rows are returned)

Nyxynyx Over a year ago

I did notice that the rows with post_timestamp earlier than 5 days were returned too... how should I change the units of -5 to -5 days?

Nyxynyx Over a year ago

WHERE post_timestamp BETWEEN (1) did not return any warning, but the rows returned were wrong, the prices include both >1000 and <1000

ajreal Over a year ago

I think my explanation is hard to understand, I have revised the answer to get straight to the point

|

john personna · Accepted Answer · 2011-12-10 17:16:22Z

1

I don't think you want to quote the numeric values.

answered Dec 10, 2011 at 17:16

john personna

4423 silver badges8 bronze badges

5 Comments

Nyxynyx Over a year ago

I removed the quotes on the numbers and its still the same results

Pekka Over a year ago

This shouldn't be the problem: mySQL will automatically convert quoted values to the required target column value - in this case, int. dev.mysql.com/doc/refman/5.0/en/type-conversion.html

john personna Over a year ago

Then my second guess ;-), is that the parenthesis after BETWEEN (XXX) confuse the parser into thinking XXX is one thing, and the following price clause is still part of the "between." UPDATE: try BETWEEN NOW() - 0 AND NOW() - 5 or BETWEEN (NOW() - 0) AND (NOW() - 5)

Nyxynyx Over a year ago

I removed the parenthesis and its still the same

Álvaro González Over a year ago

@johnpersonna - You absolutely spotted a parenthesis mismatch. You should add that to your answer.

Óscar López · Accepted Answer · 2011-12-10 17:17:56Z

1

Why is it that you're treating price as a string? I mean, there are quotes surrounding the '0' and '1000' values. Try removing them.

EDIT

Try this and tell me if it works:

SELECT listing_id, price
FROM listings
WHERE post_timestamp BETWEEN (NOW() - 0) AND (NOW() - 5)
AND price < 10000
ORDER BY post_timestamp desc

answered Dec 10, 2011 at 17:17

Óscar López

237k38 gold badges321 silver badges391 bronze badges

2 Comments

Nyxynyx Over a year ago

In field type is int(8) and I removed the quotes, but still get the same results

Pekka Over a year ago

This shouldn't be the problem: mySQL will automatically convert quoted values to the required target column value - in this case, int. dev.mysql.com/doc/refman/5.0/en/type-conversion.html

Daniel Wolfe · Accepted Answer · 2011-12-10 17:15:51Z

0

Without seeing your database schema, I can't be sure, but have you tried removing the quotes from around the numerical values?

answered Dec 10, 2011 at 17:15

Daniel Wolfe

6721 gold badge9 silver badges19 bronze badges

1 Comment

Pekka Over a year ago

This shouldn't be the problem: mySQL will automatically convert quoted values to the required target column value - in this case, int. dev.mysql.com/doc/refman/5.0/en/type-conversion.html

josegil · Accepted Answer · 2011-12-10 17:16:16Z

0

Could be possible that the field price have the value null?

answered Dec 10, 2011 at 17:16

josegil

3651 silver badge8 bronze badges

1 Comment

Nyxynyx Over a year ago

I did SELECT listing_id, price FROM (listings) WHERE price = NULL and no results were returned

Collectives™ on Stack Overflow

Filtering numbers using MySQL

5 Answers 5

6 Comments

5 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

5 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related