How can I use H2 database ROWNUM() function

classic Classic list List threaded Threaded
7 messages Options
zpp zpp
Reply | Threaded
Open this post in threaded view
|

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?
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

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
zpp zpp
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

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 <[hidden email]>:
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 Alexey Kuznetsov
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

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, 张鹏鹏 <[hidden email]> 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 <[hidden email]>:
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
zpp zpp
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

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 <[hidden email]>:
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, 张鹏鹏 <[hidden email]> 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 <[hidden email]>:
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

vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

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
zpp zpp
Reply | Threaded
Open this post in threaded view
|

Re: How can I use H2 database ROWNUM() function

How fool am I !

Max is the right way! Thanks Val



2016-05-30 19:05 GMT+08:00 vkulichenko <[hidden email]>:
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.