Hi, It could be implemented, but "ROW_NUMBER() OVER ()" is not really meant for this usage. For H2, it would actually slow down other use cases, so I think it should't be implemented in this way. Even PostgreSQL and other databases could change the behavior in some version, because this is not guaranteed I think.
Regards, Thomas On Tue, Oct 14, 2014 at 9:38 AM, Nicolas Fortin (OrbisGIS) < [email protected] <javascript:_e(%7B%7D,'cvml','[email protected]');>> wrote: > Hi, > > Yes if there is an update the row is moved at the end, like the sample I > post. This is why some database gui frontend force the sort of a column. > Maybe the implementation of H2 make such feature request nearly impossible > to accomplish ? > > -Nicolas > Le lundi 13 octobre 2014 20:04:59 UTC+2, Thomas Mueller a écrit : >> >> Hi, >> >> Does PostgreSQL makes a guarantee that two separate queries will use the >> same row number for the same logical row? I think that's an implementation >> detail. If there is a update between those calls (for example by another >> connection), the row number might be different. >> >> Regards, >> Thomas >> >> >> >> On Thursday, October 9, 2014, Nicolas Fortin (OrbisGIS) < >> [email protected]> wrote: >> >>> Hi Thomas, >>> >>> Here a complete example of the difference between Postgre and H2: >>> >>> [raw] >>> drop table if exists streets; >>> create table streets(pk int primary key, length real); >>> insert into streets values (5, 55.2), (7, 33.2), (9, 151.1), (15, 48.2), >>> (20, 14.6); >>> update streets set length = 60 where pk = 5; >>> >>> H2: >>> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() >>> OVER () rowcpt, pk ,length from streets) streetscount where length > 40 >>> >>> ROWCPT(INTEGER) PK(INTEGER) >>> LENGTH(REAL) >>> 1 5 >>> 60 >>> 2 9 151,1 >>> 3 15 48,2 >>> >>> Execute request 6/6: select * from streets >>> >>> PK(INTEGER) LENGTH(REAL) >>> 5 60 >>> 7 33,2 >>> 9 151,1 >>> 15 48,2 >>> 20 14,6 >>> >>> Postgre: >>> >>> Execute request 5/6: select rowcpt, pk, length from (select ROW_NUMBER() >>> OVER () rowcpt, pk ,length from streets) streetscount where length > 40 >>> >>> rowcpt(int8) pk(int4) >>> length(float4) >>> 2 9 151,1 >>> 3 15 48,2 >>> 5 5 >>> 60 >>> >>> Execute request 6/6: select * from streets >>> >>> pk(int4) length(float4) >>> 7 33,2 >>> 9 151,1 >>> 15 48,2 >>> 20 14,6 >>> 5 60 >>> [/raw] >>> >>> If a show a table of the full content of streets, thanks to Postgre >>> without caching primary key I could highlight the first, third and fourth >>> lines. >>> >>> Following the result of "select * from streets", I expect h2 returning >>> rowcpt 1,3,4 >>> >>> We have decided to cache primary key (take time and memory for big >>> table) in order to bypass this problem. >>> >>> Thanks for support. >>> >>> - Nicolas >>> IRSTV FR CNRS 2488 >>> >>> Le jeudi 9 octobre 2014 07:52:38 UTC+2, Thomas Mueller a écrit : >>>> >>>> Hi, >>>> >>>> I'm not sure if I understand. You basically need a row id, which >>>> uniquely identifies a row within a table? H2 supports the _rowid_ column >>>> (like SQLite). Wouldn't that be enough for you? Is the problem that the >>>> syntax (when using _rowid_) would be different to PostgreSQL? >>>> >>>> Maybe it's easier if you could provide a complete example (as small as >>>> possible, but including "create table" and "insert" statements), that shows >>>> what you need. >>>> >>>> Regards, >>>> Thomas >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Wed, Oct 8, 2014 at 4:50 PM, Nicolas Fortin (OrbisGIS) < >>>> [email protected]> wrote: >>>> >>>>> To go further I have to find a way to guess if TopTableFilter belongs >>>>> to another preparedStatement. The only difference that I found is the >>>>> Select.command is null in sub query. >>>>> >>>>> -- >>>>> 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/d/optout. >>>>> >>>> >>>> -- > 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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/d/optout.
