mysql min where statement

Question

so we have the mysql MIN() function for getting the minimum in a table...but what if I want to get a MIN() based on a criteria

so something like MIN(age) WHERE height < 170

in which the aim is to get the minimum age of ONLY people whose height is < 170...so say sam is the youngest of those with height < 170, but pam is younger than sam but have height > 170, then the query should return sam instead of pam...

how would I compose such query?

You mean the query will return 23 (Sam's age), right?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-04-17 21:33:25 +00:00
Commented Apr 17, 2011 at 21:33 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 17, 2011 at 21:33

Martin Smith · Accepted Answer · 2011-04-17 21:30:23Z

3

You say you want the "minimum age of ONLY people whose height is < 170"

SELECT MIN(age)
FROM YourTable
WHERE height < 170

Will in fact work as you require then!

WHERE limits row pre aggregation, HAVING can be used to filter on the results of aggregations.

answered Apr 17, 2011 at 21:30

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

2 Comments

Johan Over a year ago

You mean select name from yourtable where height < 170 having min(age) otherwise "Pam" will still not show up :-)

Martin Smith Over a year ago

@Johan - No, I was very careful to quote the phrase in the OP's question that my answer pertains to. If they do want the name as well they should just use WHERE, ORDER BY and LIMIT to get the first row.

Collectives™ on Stack Overflow

mysql min where statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related