Looks that this only works on a line base. The value of @y is constantly
changed. When using:
DROP TABLE IF EXISTS test
;
CREATE TABLE test(
year INT,
month INT)
;
INSERT INTO test
(year, month)
VALUES
(2000, 1),
(2000, 2),
(2000, 3),
(2000, 4),
(2000, 5),
(2000, 6),
(2001, 1)
;
SELECT @y
, year
, @y := CASE WHEN ROWNUM()=1 THEN year
WHEN @y = year THEN NULL ELSE year END
, month
FROM test
GROUP BY year
, month
ORDER BY year
, month
;
DROP TABLE test
;
I get:
2001 2000 2000 1
2000 2000 null 2
null 2000 2000 3
2000 2000 null 4
null 2000 2000 5
2000 2000 null 6
null 2001 2001 1
2014/1/14 Thomas Mueller <[email protected]>
> Hi,
>
> H2 supports "running totals" in the MySQL style. Maybe the following could
> be used as a start:
>
> drop table test;
> create table test(year int, month int);
> insert into test values(2000, 1), (2000, 2), (2000, 3), (2001, 1);
> select @y := case when rownum()=1 then year
> when @y = year then null else year end, month
> from test group by year, month order by year, month
>
> If you need more details, maybe best look / ask at StackOverflow and use
> the "MySQL".
>
> Regards,
> Thomas
>
>
>
> On Sunday, January 12, 2014, Cecil Westerhof wrote:
>
>> 2014/1/12 Atul Chowdhury <[email protected]>
>>
>>> No, I'm afraid not. What's your final output? If its a report, you may
>>> want to consider a reporting tool ( Jasper/BIRT/etc ) to satisfy this
>>> requirement.
>>>
>>
>> It is done in the web-browser. But I am making some bash scripts to do
>> things. In those I could solve it.
>>
>> --
>> 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.
>>
> --
> 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.
>
--
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.
