SQL syntax errors

Question

I am running my first SQL query and not sure where my error lies. The first line (for the date) seems to run correctly. None of the other lines seem to be working IE it returns a results with ratings of less than 3.5. I am not receiving an error message when I run the query, it runs successfully. If possible explain the 'concept' that i'm missing.

It seems like this should be a simple query, it pulls from one table 'app'. 'rating' 'downloads' and 'lastUpdate' are all columns.

SELECT * FROM app
WHERE lastUpdate >= '2011-08-01' AND 
rating >= '3.5' AND 
downloads = '1 - 5' OR '10 - 50' OR '50 - 100' OR '100 - 500'

Formatting in SQL usually puts the AND on the new line, with the next statement. Not necessary or anything, just the way it's usually done. — Jasper
– Jasper, Commented Apr 23, 2012 at 20:57
downloads = '1 - 5' OR downloads ='10 - 50' OR downloads ='50 - 100' OR downloads ='100 - 500' => You have to specify the column again — John K.
– John K., Commented Apr 23, 2012 at 20:57
When you say downloads = '1 - 5' do you mean that you want rows where the field downloads is literally '1-5' or are you trying to query where downloads is a value between 1 and 5? — JohnFx
– JohnFx, Commented Apr 23, 2012 at 20:59
@AshBurlaczenko according to me, obviously. Really, I thought that was the only way I had seen it done. I may be wrong, or may just have been looking in the wrong places, though. — Jasper
– Jasper, Commented Apr 23, 2012 at 21:04

Kevin DiTraglia · Accepted Answer · 2012-04-23 20:57:19Z

4

Try this

SELECT * FROM app
WHERE lastUpdate >= '2011-08-01' AND 
rating >= '3.5' AND 
(downloads IN ('1 - 5','10 - 50','50 - 100','100 - 500'))

I'm not sure your syntax was correct to string or values together, and if it is you need parenthesis or any of those or conditions will return true for the whole query.

answered Apr 23, 2012 at 20:57

Kevin DiTraglia

26.2k20 gold badges95 silver badges145 bronze badges

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

1 Comment

Ash Burlaczenko Over a year ago

You could also point out that rating >= '3.5' may not be want they want. '3.5' probably needs converting to a number.

JohnFx · Accepted Answer · 2012-04-23 21:03:39Z

3

You can't string together ORs like that. Try this alternate version:

SELECT * FROM app
WHERE lastUpdate >= '2011-08-01' AND 
rating >= '3.5' AND 

(downloads = '1 - 5' OR 
 downloads ='10 - 50' OR
 downloads ='50 - 100' OR 
 downloads ='100 - 500')

You can shorten this syntax a bit using the IN clause as follows.

   downloads IN('1-5','10-50','90-100','100-500')

This assumes that downloads is a varchar or text type containing values like '1-5' and that it isn't a numeric and you are expecting this to return 1,2,3,4,5.

If you mean the latter you need to specify those parameters like this

(downloads between 1 and 5 OR 
 downloads between 10 and 50 OR
 downloads between 50 and 100 OR 
 downloads between 100 and 500)

edited Apr 23, 2012 at 21:03

answered Apr 23, 2012 at 20:57

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Comments

Johnnyoh · Accepted Answer · 2012-04-23 20:58:27Z

0

SELECT * FROM app
WHERE lastUpdate >= '2011-08-01' AND 
rating >= '3.5' AND 
(downloads between 1 AND 5 OR downloads between 10 AND 50 OR downloads between 50 AND 100 OR downloads between 100 AND 500)

answered Apr 23, 2012 at 20:58

Johnnyoh

3691 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL syntax errors

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related