Hi, OK I see, what about:
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), (2002, 2), (2002, 3), ; SELECT rownum(), year, month, @prev := case when rownum()=1 then null else @prev end prev_year, case when year = @prev then null else year end changed_year, @prev := year as year FROM (select year, month from test group by year, month order by year, month) order by year, month; DROP TABLE test; Regards, Thomas On Tue, Jan 14, 2014 at 11:22 AM, Cecil Westerhof <[email protected]>wrote: > 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. > -- 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.
