Results 1 to 7 of 7
  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
    Jun 2011
    Posts
    5
    Sorry, I meant to post this on the SQ board, and will cross-post, but any replies here welcome too!

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    That's a nice one

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

  5. #5
    Join Date
    Jun 2011
    Posts
    5
    Thanks so much for that fast, that works brilliantly.

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-18-11 at 19:59. Reason: Change ORDER BY MAX(temperature) to ORDER BY max_temp

Posting Permissions

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