MySQL - Nested where conditions

Question

I have a mysql table, which is as follows:

 image_id   imagename   brandname    x       y    
    143     00003.jpg     Pirelli  147      265
    125     00003.jpg     Pirelli  500      259

Through mouse clicks I am generating x and y positions on a html canvas which I want to use to subset the table. For example, if the clicked position of x and y were 510 and 262, I want to retrieve the image id 125 as both the parameters are matching.

Suppose if the x value generated with mouse click is 510, i want to select the line with image_id 125.

I tried the following:

select * from table where imagename='00003.jpg' and 510 < max(x);

I am getting this error:

invalid use of group function.

See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2018-03-12 08:15:04 +00:00
Commented Mar 12, 2018 at 8:15 — Strawberry
– Strawberry, Commented Mar 12, 2018 at 8:15

Md. Suman Kabir · Accepted Answer · 2018-03-12 08:08:26Z

1

Max is a group function... You can't use it without having group by in it.

You are comparing X and y value so use them directly, no need for using Max

edited Mar 12, 2018 at 8:08

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

answered Mar 12, 2018 at 7:59

user769889

3762 silver badges12 bronze badges

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

Comments

Md. Suman Kabir · Accepted Answer · 2018-03-12 08:11:36Z

0

As @user769889 already mentioned, you can't use aggregate functions like Min, Max etc without using Group By that's why you are getting that error.

I think you need this :

select * from table where imagename='00003.jpg' and x < 510 and y < 262

answered Mar 12, 2018 at 8:11

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Comments

magicleon94 · Accepted Answer · 2018-03-12 08:13:41Z

0

Max is a grouping function, you cannot use it like that. You can achieve what you need by using the followind nested query:

select * from table where imagename='00003.jpg' and 510 < (select max(x) from table where imagename = '00003.jpg')

This will run the inner query that gives back the maximum value for the x column for the image, then runs the outer query using the result in its where clause!

answered Mar 12, 2018 at 8:13

magicleon94

5,2722 gold badges28 silver badges60 bronze badges

1 Comment

popeye Over a year ago

I am getting both the rows returned...instead I have to retrieve only the second line in the table....whereas to test if max(x) condition is working, when I ran the max(x) part of the code separately, it fetches correct result.

popeye · Accepted Answer · 2018-03-12 08:59:51Z

0

This is what worked for me:

select * from annotations where imagename='00003.jpg' x <= 510 order by x desc limit 1;

answered Mar 12, 2018 at 8:59

popeye

2916 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL - Nested where conditions

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related