You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by 张鹏鹏 <zh...@gmail.com> on 2016/05/25 06:56:15 UTC
How can I use H2 database ROWNUM() function
I want to get rownum from the sql query.
But when I use the H2 database ROWNUM() function
I got Exception.
Caused by: class org.apache.ignite.IgniteCheckedException: Unsupported
expression: ROWNUM() [type=Rownum]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1782)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:799)
... 32 more
Caused by: class org.apache.ignite.IgniteException: Unsupported expression:
ROWNUM() [type=Rownum]
Can I get rownum from sql qeury someway?
Re: How can I use H2 database ROWNUM() function
Posted by 张鹏鹏 <zh...@gmail.com>.
How fool am I !
Max is the right way! Thanks Val
2016-05-30 19:05 GMT+08:00 vkulichenko <va...@gmail.com>:
> Hi,
>
> Window functions are not supported, but it looks like you're simply
> querying
> maximum balance for each group. Am I missing something?
>
> In this case you can execute something like this:
>
> SELECT yyyappid, max(balance) FROM ... GROUP BY yyyappid
>
> Will this work for you?
>
> -Val
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/How-can-I-use-H2-database-ROWNUM-function-tp5161p5307.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: How can I use H2 database ROWNUM() function
Posted by vkulichenko <va...@gmail.com>.
Hi,
Window functions are not supported, but it looks like you're simply querying
maximum balance for each group. Am I missing something?
In this case you can execute something like this:
SELECT yyyappid, max(balance) FROM ... GROUP BY yyyappid
Will this work for you?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-use-H2-database-ROWNUM-function-tp5161p5307.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I use H2 database ROWNUM() function
Posted by 张鹏鹏 <zh...@gmail.com>.
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 <ak...@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, 张鹏鹏 <zh...@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 <va...@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
>
Re: How can I use H2 database ROWNUM() function
Posted by Alexey Kuznetsov <ak...@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, 张鹏鹏 <zh...@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 <va...@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
Re: How can I use H2 database ROWNUM() function
Posted by 张鹏鹏 <zh...@gmail.com>.
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 <va...@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.
>
Re: How can I use H2 database ROWNUM() function
Posted by vkulichenko <va...@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.