2014/1/10 Noel Grandin <[email protected]>
>
>
> On 2014-01-09 23:43, Cecil Westerhof wrote:
>
>> This works fine, but I would prefer to only have Date shown when it is
>> different as the previous Date. Is this possible?
>>
>
> Depends on what you mean by "previous date"?
> You didn't show the table definition, so it's hard to know.
>
> In general, filtering when you are using GROUP BY means using a HAVING
> clause.
>
The definition (that is important, I removed 16 fields) is:
CREATE TABLE vmstatDefault (
date DATE DEFAULT CURRENT_DATE() NOT NULL,
time TIME DEFAULT CURRENT_TIME() NOT NULL,
idleTime INT,
PRIMARY KEY (date, time)
);
I use:
SELECT date AS Date
, SUBSTRING(time, 1, 2) AS Hour
, MIN(idleTime) AS `Min Idle`
, MAX(idleTime) AS `Max Idle`
, COUNT(*) AS SUM
FROM vmstatDefault
GROUP BY Date
, Hour
ORDER BY Date DESC
, Hour DESC
This gives:
DATE HOUR MIN IDLE MAX IDLE SUM
2014-01-11 09 87 95 45
2014-01-11 08 93 95 60
2014-01-11 07 94 95 60
2014-01-11 06 95 95 60
2014-01-11 05 95 95 60
2014-01-11 04 94 95 60
2014-01-11 03 95 95 60
2014-01-11 02 95 95 60
2014-01-11 01 95 95 60
2014-01-11 00 95 95 60
2014-01-10 23 86 96 60
2014-01-10 22 87 97 60
2014-01-10 21 92 97 60
But I would like to get:
DATE HOUR MIN IDLE MAX IDLE SUM
2014-01-11 09 87 95 45
08 93 95 60
07 94 95 60
06 95 95 60
05 95 95 60
04 94 95 60
03 95 95 60
02 95 95 60
01 95 95 60
00 95 95 60
2014-01-10 23 86 96 60
22 87 97 60
21 92 97 60
That I can not do what HAVING (as far as I know). I have done something
like this about 15 years ago. But it is possible that this was an extension
to SQL and not standard SQL. I just do not remember what I did. :-(
--
Cecil Westerhof
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
