CODEWITHSUNDEEP
mysql
user1050619
user1050619

Reputation: 20856

mysql select max value of unique items

I have this table and need to pic the city with the max length and min length.

In this query Im trying to get the max length

enter image description here

select x.name, x.len from
(select name, char_length(name)  as len
from tutorials.city) x
where x.len = (select max(x.len) from x)

This query works with aggregator in where clause - 

select x.name, x.len from
(select name, char_length(name)  as len
from tutorials.city) x
where x.len = (select max(id) from tutorials.city)

Upvotes: 0

Views: 44

Answers (2)

Barmar
Barmar

Reputation: 780818

You can't use an aggregation function in the WHERE clause. Aggregation isn't done until all the rows are selected, and WHERE is used to select the rows.

You can use HAVING instead.

select x.name, x.len from
(select name, char_length(name)  as len
from tutorials.city) x
HAVING x.len = MAX(x.len)

See SQL - having VS where

Upvotes: 1

DanB
DanB

Reputation: 2104

For max length :

select name
from tutorials.city
order by length(city) desc
limit 1;

For min length :

select name
from tutorials.city
order by length(city)
limit 1;

Upvotes: 0

Related Questions