SQL using (min) and (max)

paulcurtis · #1 (**permalink**) 06-18-11, 06:36

This is driving me nuts. I do hope someone can help this poor old duffer out.

Let's say we have the following two column database of city daily temperatures for a certain month. We can assume no two temperatures will ever be exacty the same. We don't know how many cities or rows there are:

London 20.1
Boston 15.5
Lincoln 18.1
Lincoln 13.0
London 21.1
Lincoln 12.9
Boston 17.6
London 20.3

If I do the following query:

select CITY, max(TEMP) MAXTEMP
from DATABASE
group by CITY

I will get:

CITY MAXTEMP
London 21.1
Boston 17.6
Lincoln 18.1

That's fine. Now let's say, out of those, I want the row with the MINIMUM temperature,.i.e.

Boston 17.6

I thought if I put the above query into a subquery, this would work:

select CITY, min(MAXTEMP)
from
(select CITY, max(TEMP) MAXTEMP
from DATABASE
group by CITY)

but it is syntactically incorrect. What am I doing wrong please?

Thanks.

Paul.

paulcurtis · #2 (**permalink**) 06-18-11, 06:38

Sorry, I meant to post this on the SQ board, and will cross-post, but any replies here welcome too!

tonkuma · #3 (**permalink**) 06-18-11, 08:00

Here is an example on DB2.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 city_daily_temperature
(city , temperature) AS (
VALUES
  ('London'  , 20.1)
, ('Boston'  , 15.5)
, ('Lincoln' , 18.1)
, ('Lincoln' , 13.0)
, ('London'  , 21.1)
, ('Lincoln' , 12.9)
, ('Boston'  , 17.6)
, ('London'  , 20.3)
)
SELECT city
     , max_temp
 FROM  (SELECT city
             , MAX(temperature) AS max_temp
             , MIN( MAX(temperature) )
                  OVER()        AS min_max_temp
         FROM  city_daily_temperature
         GROUP BY
               city
       )
 WHERE max_temp = min_max_temp
;
------------------------------------------------------------------------------

CITY    MAX_TEMP
------- --------
Boston      17.6

  1 record(s) selected.

shammat · #4 (**permalink**) 06-18-11, 08:08

That's a nice one

I didn't think of combining the min/max into a single query

paulcurtis · #5 (**permalink**) 06-18-11, 08:44

Thanks so much for that fast, that works brilliantly.

umayer · #6 (**permalink**) 06-18-11, 17:18

you also might try this:

select CITY, MAXTEMP
from
(select CITY, max(TEMP) MAXTEMP
from DATABASE
group by CITY) A

ORDER BY MAXTEMP FETCH FIRST ROW ONLY

tonkuma · #7 (**permalink**) 06-18-11, 18:57

If you used "FETCH FIRST 1 ROW ONLY",
subquery would be unnecessary.

Tested on DB2 Express-C 9.7 FP4 for Windows.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 city_daily_temperature
(city , temperature) AS (
VALUES
  ('London'  , 20.1)
, ('Boston'  , 15.5)
, ('Lincoln' , 18.1)
, ('Lincoln' , 13.0)
, ('London'  , 21.1)
, ('Lincoln' , 12.9)
, ('Boston'  , 17.6)
, ('London'  , 20.3)
)
SELECT city
     , MAX(temperature) AS max_temp
 FROM  city_daily_temperature
 GROUP BY
       city
 ORDER BY
       max_temp
 FETCH FIRST 1 ROW ONLY
;
------------------------------------------------------------------------------

CITY    MAX_TEMP
------- --------
Boston      17.6

  1 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech