SQL using (min) and (max)

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

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.

shammat · #2 (**permalink**) 06-18-11, 07:24

Code:

SELECT city,
       max_temp as min_temp
FROM (
    SELECT city, 
           max_temp,
           row_number() over (order by max_temp asc) as row_num
    FROM (
       SELECT city,
              max(temp) as max_temp
       FROM your_table
       GROUP BY city
    ) tm
) t
WHERE row_num = 1

An alternative would be:

Code:

WITH max_temperatures AS (
   SELECT city,
          max(temp) as max_temp
   FROM temperature
   GROUP BY city
) 
SELECT * 
FROM max_temperatures
WHERE max_temp = (SELECT min(max_temp)
                  FROM max_temperatures)

paulcurtis · #3 (**permalink**) 06-18-11, 08:48

Thanks very much for that shammat, as you saw I had a reply in the DB2 forum too, so I have three solutions now

healdem · #4 (**permalink**) 07-04-11, 06:16

please do not multipost the same question in several forums
it causes confusion
it it may not lead to the right answer
it may pee off contributors so they don't respond to your questions

...also FWIW a new question requires a new thread

dbGuyo · #5 (**permalink**) 08-17-11, 06:16

The other solution with just ANSI SQL would be:

Code:

select CITY2, temp2
from (select city city2, max(TEMP) temp2
      from t1
      group by CITY) a
where temp2 <= ALL (select max(TEMP) MAXTEMP
                    from t1
                    group by CITY);

But I don't claim that this is more efficient

Internet Services

Web Development

Digital Marketing

Consumer Tech