CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Ni Yao
Ni Yao

Reputation: 40

SQL - Unable to filter table using multiple aggregated function results

I'm looking for the explanation on why the following code doesn't work. Specifically the line
lc=MIN(lc) OR lc=MAX(lc)
Isn't this just comparing two numbers?

SELECT city, lc FROM  
    (SELECT city, LENGTH(city) AS lc FROM station) AS t1   
GROUP BY city 
HAVING lc=MIN(lc) OR lc=MAX(lc)

Edited to remove a lot of the info since people were not reading my question but looking at my code and then providing a fix for it, which I don't need. I want an answer to my question about SQL concept/theory.

Upvotes: 0

Views: 47

Answers (2)

Mike D.
Mike D.

Reputation: 4104

You can use an INNER JOIN to filter your table to a subquery that finds the min and max lengths.

SELECT s.city, LENGTH(s.city) AS city_length
FROM station AS s
INNER JOIN (
    SELECT MIN(LENGTH(city)) AS min_length, MAX(LENGTH(city)) AS max_length
    FROM station
) AS a ON LENGTH(s.city) IN (a.min_length, a.max_length);

Of course if there are ties for the min and max lengths you'll get all of those entries.

Upvotes: 1

Ian Kenney
Ian Kenney

Reputation: 6426

You can try something like 

SELECT city, LENGTH(city) AS lc FROM station   
WHERE length(city) in (
  SELECT max(length(city)) FROM station
    union  
  SELECT max(length(city)) FROM station
)

Upvotes: 0

Related Questions