ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 张鹏鹏 <zhangpp...@gmail.com>
Subject Re: How can I use H2 database ROWNUM() function
Date Thu, 26 May 2016 03:34:12 GMT
Hi,
thank you!

This's the schema.

  id character varying(50) NOT NULL,
  openid character varying(50) NOT NULL,
  yyyappid character varying(50) NOT NULL,
  logtime timestamp without time zone DEFAULT now(),
  clienttime timestamp without time zone NOT NULL,
  logaction character varying(10) NOT NULL,
  logtype character varying(10),
  spend bigint,
  addvalue bigint,
  sendvalue bigint,
  uservc bigint,
  clientorderid character varying(50) NOT NULL,
  lotteryid character varying(50),
  ttyesendtype smallint,
  note character varying(1000),
  userinfoid character varying(100),
  clientname character varying(100),
  clientbusinessname character varying(100)




It' a log table.It record every transaction.
I want select every group't(yyyapppid) top   balance owner(addvalue  +
sendvalue)

I can implement it in PostgreSQL this way:


select * from (select t.* ,row_number() OVER(PARTITION BY t.yyyappid ORDER
BY t.balance DESC)
 rn from (select SUM(addvalue + sendvalue) as balance ,openid,yyyappid from

Userinfologs where  logtime>'2016-5-25 00:00:00' and logtime<'2016-5-26
00:00:00'
 group by openid,yyyappid order by balance desc) as t) as ot where ot.rn =1


So,I think if I implement it as an  "Continuous Queries",I can get the top
one of every group timely.
Is't the right way to use "Continuous Queries"?



2016-05-26 11:00 GMT+08:00 Alexey Kuznetsov <akuznetsov@gridgain.com>:

> I'm not sure what are you trying to implement, but may be
>  select * from SomeTable where someGroup = 'someValue1' order by
> someColumn limit 1
> union
>  select * from SomeTable where someGroup = 'someValue2' order by
> someColumn limit 1
> union
> ....
>
> Will work?
>
> Could you provide you db schema, may be we could give a better answer,
>
> On Thu, May 26, 2016 at 9:12 AM, 张鹏鹏 <zhangpp520@gmail.com> wrote:
>
>> Hi,
>> I want to implement "Get top 1 row of each group" program.
>> I think If I could use Continuous Queries and "window Functions" in
>> sql,it's easier.
>>
>>
>> 2016-05-25 21:30 GMT+08:00 vkulichenko <valentin.kulichenko@gmail.com>:
>>
>>> Hi,
>>>
>>> ROWNUM is not supported, and I'm not sure it's possible to properly
>>> implement it in the distributed environment.
>>>
>>> How do you intend to use it?
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://apache-ignite-users.70518.x6.nabble.com/How-can-I-use-H2-database-ROWNUM-function-tp5161p5190.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>
>
> --
> Alexey Kuznetsov
> GridGain Systems
> www.gridgain.com
>

Mime
View raw message