Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: SQL using (min) and (max)

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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)
    Last edited by shammat; 06-18-11 at 08:29.

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    Thanks very much for that shammat, as you saw I had a reply in the DB2 forum too, so I have three solutions now

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2011
    Posts
    14
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •