12-18-2007 09:34 AM
12-18-2007 10:05 AM
07-12-2011 05:49 PM
Add the group by statement to your sql code.
If you're doing in PROC SQL without pass through
create table top_keys as
select key, max(id) as max_value
group by id;
I don't know why Peter is using the having clause, but I don't think you need it unless you want the max for each group in each row as well. If you do then can remerge in a second sql step to avoid the issue Peter has mentioned.
Generally, any variables in the group by statement can be included in the select statement.
07-18-2013 12:06 PM
I think I have the same question and similar code but it does not seem to be working.
CREATE TABLE WORK.TD_TST AS
(MAX(t1.read_time)) FORMAT=DATETIME22.3 AS MAX_of_read_time,
/* start_date */
) LABEL="start_date" AS start_date
FROM PBGMES.td_header t1
GROUP BY t1.sub_id,
/* having t1.read_time = (MAX(t1.read_time))*/
id read_time max_read_time pmax_derated start_date
130716041724 17JUL2013:13:07:49.000 17JUL2013:13:07:49.000 0 -25200
130716041724 17JUL2013:14:54:01.000 17JUL2013:14:54:01.000 89.420417786 -25200
07-18-2013 12:44 PM
I am not sure why you believe the query is not working, I see two records with the same sub_id and different pmax_derated values, so group by could be working. But I wonder if the calculation of start_date is what you intended.
I see that you use 'hour' as the interval in intnx and then specify today's date as the start-from.. The today() function returns a date value; so there is a mis-match here.
If you are trying to specify 12:00 noon yesterday as the start time, then you will need something like (intnx('HOUR',dhms(TODAY(),0,0,),-12) The DHMS converts today's date value to a datetime value which the 'HOUR' interval will work on.
Hope this helps.
07-18-2013 01:56 PM
I just followed my colleagues without thinking completely. I think if I remove the field pmax_derated in the group by field, I get what I want - single record for sub_id.