Select nested Mysql

Question

I’m trying to get the most value out of the smallest of a table, the query I’m trying to execute is as follows:

SELECT district FROM
    (SELECT district, MIN(postal_code)
    FROM address
    WHERE postal_code != ''
    GROUP BY district)
WHERE MAX(postal_code);

Error: #1248 - Every derived table must have its Own alias

Answer 1

There are some problems in your sentence. First I will put as it is corrected, then I will explain:

SELECT district FROM
  (SELECT district, min_postal_code from
    (SELECT district, MIN(postal_code) as min_postal_code
    FROM address
    WHERE postal_code != ''
    GROUP BY district) tabela
   GROUP BY district
   HAVING min_postal_code = MAX(min_postal_code)) tabela2;

• Subselects need to be identified by a name. In this case, I used tabela;
• Aggregation functions, such as MIN, do not return good column names. So I used as min_postal_code;
• WHERE requires some test condition. Only MAX(postal_code) does not test anything. I put the test I imagine you would like to do, which is to catch the district with the largest zip code (another subselect);
• I don’t know how Mysql manages tables SELECTnested, but it is clear to me that the scope of the nested table does not exist within WHERE, so I nested twice using HAVING, which is the condition of testing aggregation functions.