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!

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.

You define that rows shall be grouped by
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
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.

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...

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.

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

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).

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: