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.

Reply via email to