Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Max and group by gives error

    I have a query that looks like this:

    SELECT
    "Present_Value" AS "Meter_Reading",
    to_date(substr("Timestamp",2,12),'YYMMDDHH24MISS') AS "Time_Stamp",
    Month(to_date(substr("Timestamp",2,12),'YYMMDDHH24 MISS')) AS "Time_Stamp_Month"
    FROM theTable
    WHERE ("Name" = 'WhatIAmLookingFor')

    This works just fine and gets all the readings along with the time stamp info.

    But I would like to have it only return the largest reading per month along with that reading's time stamp info. So I change it to:

    SELECT
    MAX("Present_Value") AS "Meter_Reading",
    to_date(substr("Timestamp",2,12),'YYMMDDHH24MISS') AS "Time_Stamp",
    Month(to_date(substr("Timestamp",2,12),'YYMMDDHH24 MISS')) AS "Time_Stamp_Month"
    FROM theTable
    WHERE ("Name" = 'WhatIAmLookingFor')
    GROUP BY Month(to_date(substr("Timestamp",2,12),'YYMMDDHH24 MISS'))

    and then it fails. The error is:

    CDHFD0812E: Error executing SQL statement. An expression starting with "Timestamp" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.. SQLCODE=-119, SQLSTATE=42803, DRIVER=3.50.152

    If I leave out the

    to_date(substr("Timestamp",2,12),'YYMMDDHH24MISS') AS "Time_Stamp",

    then it works (though it doesn't of course return the timestamp information).

    The error message implies that I need to have the Time_Stamp part in the GROUP BY but I an new to writing SQL queries and I don't see why. Adding the timestamp to the GROUP BY gets the wrong results. I only need it grouped by month.

    Can anyone help? Thanks for any tips!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Apply MAX function to "Time_Stamp" column, like "Meter_Reading" column.

    Including the expression of "Meter_Reading" into GROUP BY clause would be another way to remove the error.
    But, the query result might be different.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You define that rows shall be grouped by
    Code:
    Month(to_date(substr("Timestamp",2,12),'YYMMDDHH24 MISS'))
    Each group will produce a single output row from multiple rows belonging to that group. Thus, you have to define what shall happen with the values in the different columns. You can apply an aggregate function like MAX or MIN, for example, or include the column in the GROUP BY clause. But the column defined by
    Code:
    to_date(substr("Timestamp",2,12),'YYMMDDHH24MISS') AS "Time_Stamp"
    is neither the one nor the other. So DB2 simply cannot now what it should do with multiple values produced here in each group and tells you that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some basic principles of SQL(in DB2).

    1) Column names shoud not be enclosed by double quotation marks in CREATE TABLE statement.
    For example: Use time_stamp instead of "Timestamp".

    2) Use(create table with) appropriate data types.
    For example: data type of "Timestamp" column sould be TIMESTAMP.
    (I guessed you used CHAR(xx) or VARCHAR(xx) for the "Timestamp" column.)

    Then, your query would be like...
    Code:
    SELECT MAX(present_value) AS "Meter Reading"
         , MAX(time_stamp)    AS "Time Stamp"
         , MONTH(time_stamp)  AS "Time Stamp Month"
     FROM  the_table
     WHERE name = 'WhatIAmLookingFor'
     GROUP BY
           MONTH(time_stamp)
    ;
    Last edited by tonkuma; 06-08-11 at 19:21.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can see the discussions about using double quotation marks in this thread.
    Column names with spaces - How could db2 allow its creation? | comp.databases.ibm-db2 | Google Groups

    Lennart Jonsson, Serge Rielau and Joe Celko gave good explanations.

  6. #6
    Join Date
    Jun 2011
    Posts
    4
    Thanks, I used your advice to fix things up.

    But now a followup question. Suppose I want the highest reading and the timestamp for which it occurred? The above query will work if the readings always increase but not if they go up and down.

    If my data is

    Code:
    reading  timestamp
    100       6/1
    110       6/2
    190       6/3
    110       6/3
    190       6/4
    150       6/4
    150       6/5
    I'd like to get either one of, and only one of, (190, 6/3) or (190, 6/4) back and not (190, 6/5).

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... to get either one of, and only one of, (190, 6/3) or (190, 6/4) back ...
    Please see "Retrieving MAXimum row"
    in SQL on Fire! Part 1http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

    If you want to get both of (190, 6/3) and (190, 6/4),
    a way is to use RANK OLAP specification.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
    the_table(reading , time_stamp) AS (
    VALUES
      ( 100 , DATE('6/1/2011') )
    , ( 110 , DATE('6/2/2011') )
    , ( 190 , DATE('6/3/2011') )
    , ( 110 , DATE('6/3/2011') )
    , ( 190 , DATE('6/4/2011') )
    , ( 150 , DATE('6/4/2011') )
    , ( 150 , DATE('6/5/2011') )
    )
    SELECT reading
         , time_stamp
     FROM  (SELECT t.*
                 , RANK() OVER(ORDER BY reading DESC) AS rank_reading
             FROM  the_table t
           ) s
     WHERE rank_reading = 1
    ;
    ------------------------------------------------------------------------------
    
    READING     TIME_STAMP
    ----------- ----------
            190 2011-06-03
            190 2011-06-04
    
      2 record(s) selected.

Posting Permissions

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