Is there a way to use sql functions like min as values in where clause of mysql?

Question

i am having problem with the mysql statements... the answer is to get the name of city and length of city of min length from a table named station where ordered by alphabetically by city names i have tried some statements as described below.

SELECT City, LENGTH(City) FROM Station ORDER BY City WHERE LENGTH(City) = MIN(LENGTH(City));

I have also tried to use user defined varibales like below:

SELECT @min := MIN(LENGTH(City)) FROM Station ORDER BY City;

SELECT City, LENGTH(City) FROM Station ORDER BY City WHERE LENGTH(City) = @min;

Sample Output: Amo 3

it showing this error : check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE LENGTH(City) = @min'

But it's not working please help

question from:https://stackoverflow.com/questions/65868423/is-there-a-way-to-use-sql-functions-like-min-as-values-in-where-clause-of-mysql

Answer 1

I have got this solution. I can use complex sql statements to equalize the length of the city to the minimun length by using the sql statements below:

SELECT City, LENGTH(City) FROM Station WHERE LENGTH(City) = (SELECT MIN(LENGTH(City)) FROM Station) ORDER BY City LIMIT 1;