CODEWITHSUNDEEP
mysql
Pattara Woraphu
Pattara Woraphu

Reputation: 3

Mysql search for specific on criterias

I am coding mysql in rstudio and I want to find a row which shows minimum weight_g(weight in grams). Then I use a below command but it shows errors.

Q("select * from bl where Weight_g = min(Weight_g)") [1] "HY000 1111 [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.17-log]Invalid use of group function"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select * from bl where Weight_g = min(Weight_g)'"

I am stuck with this error message and any help is greatly appreciated! Thank you!

Upvotes: 0

Views: 36

Answers (2)

John Woo
John Woo

Reputation: 263703

You can get the minimum value inside subquery,

select * 
from bl 
where Weight_g = (SELECT min(Weight_g) from bl)

if you don't care about duplicate and wanted to get only one value, then ORDER BY and LIMIT will suffice,

select * 
from bl 
ORDER BY Weight_g ASC
LIMIT 1

Upvotes: 1

cdaiga
cdaiga

Reputation: 4939

Try this without duplicates:

select * 
from bl 
ORDER BY Weight_g ASC
LIMIT 1;

Or these to get all the different instances with minimum weights:

select A.*
from bl A
where 
A.weight_g=(select weight_g from bl ORDER BY weight_g ASC
LIMIT 1);

Or:

select A.*
from bl A
where 
A.weight_g=(select min(weight_g) from bl);

Upvotes: 0

Related Questions