0

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)
user1050619
  • 19,822
  • 85
  • 237
  • 413

2 Answers2

1

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

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

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;
DanB
  • 2,022
  • 1
  • 12
  • 24
  • Thanks...What is the problem with the above query? Cant I refer to the inner table on the when clause? – user1050619 Aug 17 '18 at 20:17
  • You can't use aggregation functions in the `WHERE` clause. Aggregation is done *after* selecting all the rows. – Barmar Aug 17 '18 at 20:29
  • My query will return the longest city name. If 2 city are equals, you'll get only one. That's you want, or you want the 2? – DanB Aug 17 '18 at 20:29