Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 4AD45200AE4 for ; Thu, 26 May 2016 05:34:18 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 49A4E160A2E; Thu, 26 May 2016 03:34:18 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 3FFEF160A29 for ; Thu, 26 May 2016 05:34:17 +0200 (CEST) Received: (qmail 64352 invoked by uid 500); 26 May 2016 03:34:16 -0000 Mailing-List: contact user-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ignite.apache.org Delivered-To: mailing list user@ignite.apache.org Received: (qmail 64342 invoked by uid 99); 26 May 2016 03:34:16 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 26 May 2016 03:34:16 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id B9DF9C0F86 for ; Thu, 26 May 2016 03:34:15 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.429 X-Spam-Level: * X-Spam-Status: No, score=1.429 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_ENVFROM_END_DIGIT=0.25, HTML_MESSAGE=2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id DU76JCRS_EAO for ; Thu, 26 May 2016 03:34:13 +0000 (UTC) Received: from mail-ig0-f178.google.com (mail-ig0-f178.google.com [209.85.213.178]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 7F55A5F23D for ; Thu, 26 May 2016 03:34:13 +0000 (UTC) Received: by mail-ig0-f178.google.com with SMTP id bi2so84035688igb.0 for ; Wed, 25 May 2016 20:34:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to; bh=7PacPGcouqbN1mLmK/06my0zYX3LRYZDMyhTd1aIlXo=; b=fc8lb9KLRtxGOw4bjGewWqy5CflmFWdp59DMe5DjYeEbSIy7cxKjNGWcKZtG/nQLr7 Ion87oADr7gaR4JJ4y3cBIzvCAZUm9elyVKAof+V0WoQCAl/ivLCp2ijnXKpvSqrSSaU JWnZpDk3Jt5rjvpvNxfrXUYVAk2zTzJkAWNqWt9SKK94aLpUO65M8tI3UUPlTiNKYA8L 8s6iHtw/2uwxkd2CDIoYTuqXJ286EdFl27y5MQO9vymdenuZXzDr0T2+3TfloqLwDzx2 D9K4iS9wmPtfAxa+H1BWNacVhU4DFPido1Xf5Hf//MKsGQylA1sgs7XqgOFT82eiwpd/ GL/Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=7PacPGcouqbN1mLmK/06my0zYX3LRYZDMyhTd1aIlXo=; b=JEZhbs5Y4sygMf2ebcYgxBK2/GPCDLkN0KQn7Vyrz7CMBIRaWfHGwUVZ+8aCufhZFt KDES7062nL0uBU3wozJgtvbJAiJ0JoZQvOEUziyy/M144MgFWF2Z6h8yW91v6KxZzjeZ bEDTaZj0mdnU7+ysInRChEg3KOHBZOzJlbrdqYTqcgNOdMSGppjCmlKjVy6pSn9UmYvF 7rKgwJh9vS86fRQR6fk0r9R1srdXGIwgB8kbK2WHMH5YIY/FVDKj1yWmnqZEupQAaO5A awQIRSth+rYbK/uJHE0F3+4yT6/QfA2sLLLDW7+PZpsJ0KIp2EGY1yyTJyKDvy/g45pV xFYg== X-Gm-Message-State: ALyK8tJuI3sEEcUtsR88XnpUvq1G+G4qcN7gRHwseyVHDvbO6upwo5zi2Cts0nXajG7+ap48hF2BjKx3hyu6kA== MIME-Version: 1.0 X-Received: by 10.50.136.136 with SMTP id qa8mr1203060igb.62.1464233652327; Wed, 25 May 2016 20:34:12 -0700 (PDT) Received: by 10.79.118.156 with HTTP; Wed, 25 May 2016 20:34:12 -0700 (PDT) In-Reply-To: References: <1464183039283-5190.post@n6.nabble.com> Date: Thu, 26 May 2016 11:34:12 +0800 Message-ID: Subject: Re: How can I use H2 database ROWNUM() function From: =?UTF-8?B?5byg6bmP6bmP?= To: user@ignite.apache.org Content-Type: multipart/alternative; boundary=089e01536f8cb71a410533b67670 archived-at: Thu, 26 May 2016 03:34:18 -0000 --089e01536f8cb71a410533b67670 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 = =3D1 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 : > I'm not sure what are you trying to implement, but may be > select * from SomeTable where someGroup =3D 'someValue1' order by > someColumn limit 1 > union > select * from SomeTable where someGroup =3D '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, =E5=BC=A0=E9=B9=8F=E9=B9=8F 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 : >> >>> 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-databas= e-ROWNUM-function-tp5161p5190.html >>> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >>> >> >> > > > -- > Alexey Kuznetsov > GridGain Systems > www.gridgain.com > --089e01536f8cb71a410533b67670 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,
thank you!

This's the= =C2=A0schema.

=C2=A0 id character varying(50) NOT NULL,
=C2=A0 openid character varying(50)= NOT NULL,
=C2= =A0 yyyappid character varying(50) NOT NULL,
=C2=A0 logtime timestamp without time zone DEF= AULT now(),
=C2= =A0 clienttime timestamp without time zone NOT NULL,
=C2=A0 logaction character varying(10)= NOT NULL,
=C2= =A0 logtype character varying(10),
=C2=A0 spend bigint,
=C2=A0 addvalue bigint,
=C2=A0 sendvalue bigint,
=C2=A0 uservc bigint,
=C2=A0 clientorderid charac= ter varying(50) NOT NULL,
=C2=A0 lotteryid character varying(50),
=C2=A0 ttyesendtype smallint,
=C2=A0 note character varyi= ng(1000),
=C2=A0= userinfoid character varying(100),
=C2=A0 clientname character varying(100),
<= div style=3D"">=C2=A0 clientbusinessname cha= racter varying(100)

<= br>


It' a log table.It r= ecord every=C2=A0transaction.
I want select every group't(yyy= apppid) top =C2=A0 balance owner(addvalue=C2=A0 +=C2=A0sendvalue)

I can implement it i= n=C2=A0PostgreSQL this way:


se= lect * from (select t.* ,row_number() OVER(PARTITION BY t.yyyappid ORDER BY= t.balance DESC)
=C2=A0rn from (select SUM(addvalue + sendvalue) = as balance ,openid,yyyappid from =C2=A0
Userinfologs where =C2=A0= logtime>'2016-5-25 00:00:00' and logtime<'2016-5-26 00:00= :00'=C2=A0
=C2=A0group by openid,yyyappid order by balance de= sc) as t) as ot where ot.rn =3D1


<= div>So,I think if I implement it as an =C2=A0"Continuous Queries"= ,I can get the top one of every group=C2=A0timely.
Is't the r= ight way to use "Continuous Queries"?


201= 6-05-26 11:00 GMT+08:00 Alexey Kuznetsov <akuznetsov@gridgain.com>:
I'm no= t sure what are you trying to implement, but may be
=C2=A0select * from= SomeTable where someGroup =3D 'someValue1' order by someColumn lim= it 1
union
=C2=A0select * from SomeTable where som= eGroup =3D '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, =E5=BC=A0=E9=B9= =8F=E9=B9=8F <zhangpp520@gmail.com> wrote:
Hi,
I want to implement "Get = top 1 row of each group" program.
I think If I could use=C2= =A0Continuous Queries and "window Functions" in sql,it's easi= er.


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.nabb= le.com/How-can-I-use-H2-database-ROWNUM-function-tp5161p5190.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.




<= /div>--
Alexey Kuzn= etsov
GridGain Systems
www.gridgain.com

--089e01536f8cb71a410533b67670--