Re: [sqlite] How to get row number of an ID in sorted results

[Igor Tandetnik]

On 11/14/2016 2:23 AM, Mike King wrote:

Thanks. In answer to your question a page of results is displayed in a grid
on the screen. The user selects a row. If they then change the sort order I
want to show the page that contains the selected row (and reselect it).
Each row has a unique ID and i need to know the position in the result set
so I can calculate the page.


Unless you sort on a set of fields that together form a unique value 
(e.g. "order by Value2, ID"), you cannot predict beforehand where a 
particular row lands, without actually performing the sort and scanning 
through the resultset.


For example, imagine that, in the limit, all rows have the same value in 
Value2. If you only do "order by Value2", then the resulting order will 
be completely indeterminate; there's no telling where a row with a given 
ID would land.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Teg]

Hello Mike,


What I do is I note the currently selected ID prior to the sort, then
sort using a query and return the ID's in the new sorted order. Then
in the program I search for the ID and display the selected line again
in my list control. Basically, I maintain in memory a complete list of
the  ID's  in  a vector in the currently selected order. The vector is
also  used  when  it's  time  to  resolve because I have a 1:1 mapping
between the index of the item and the ID of the item.

The  demand-load  happens  for the visible page so, when I navigate to
the selected item, that page resolves against the DB.

This  only  works  because I control the program. It probably wouldn't
work for a web page or something like that. 






Sunday, November 13, 2016, 12:29:12 PM, you wrote:

MK> I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text
MK> and Value2 - Text). After doing an order by in a select query I'd like to
MK> know the row number that contains a particular ID.

MK> (The real world use is this: I have an application which displays paged
MK> lists of results. If you change the sort order I'd like the application to
MK> go to the page that contains the current selected ID. To do this I need to
MK> know what is the position in the sorted list of the ID).

MK> So, after some experimentation, I'm using a temporary table to hold the
MK> ordered IDs and then getting the rowid of the row with the ID I want. (in
MK> this example the list is sorted by Value2 and the selected ID=1):

MK> create temporary table TempIDs as select ID from Test order by Value2;
MK> select rowid from TempIDs where ID = 1;
MK> drop Table TempIDs;

MK> I know SQL light doesn't support rownum (like Oracle) but is there any way
MK> I could simplify this using a CTE so I don't have to create the temp table?
MK> All I really want is the number of the row with the ID in the sorted list.

MK> Cheers,

MK> Reply
MK> Forward
MK> ___
MK> sqlite-users mailing list
MK> sqlite-users@mailinglists.sqlite.org
MK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Simon Slavin]

On 14 Nov 2016, at 7:23am, Mike King  wrote:

> Thanks. In answer to your question a page of results is displayed in a grid
> on the screen. The user selects a row. If they then change the sort order I
> want to show the page that contains the selected row (and reselect it).
> Each row has a unique ID and i need to know the position in the result set
> so I can calculate the page.

This can be done but it requires a lot of programming.  If the user changes the 
sort order you then have to do two searches related to the values of the new 
key in the selected row. Which means that your software needs to be able to 
look up the screen column they're sorting on and know which table columns 
that's related to.

Assuming a window showing 25 rows at once.
Assuming that each row has a 'rowid' value.
Assuming that you know the 'rowid' value for the currently selected row.
Assuming you know the name of the table column which corresponds to the sort 
order

Look up the selected row and find the value of the sorting table column.
Search backwards in that table, sorted on that row, and find the previous 12 
rows.
Search forwards in that table, sorted on that row, and find the following 12 
rows.
Display all 25 of those rows.
Remember the first value of the first search in case they scroll up.
Remember the last value of the second search in case they scroll down.

You're not going to do this in a couple of lines of your programming language 
or a couple of lines of SQL.  And it's complicated enough that I might write it 
as a library rather than writing a custom version which works only for one 
screen of one program.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Wout Mertens]

An out-of-the-box question: why do you need to have pages at all? I as a
user never cared about the exact page a result was on.

Take a look at http://use-the-index-luke.com/no-offset which explains how
to do keyset pagination.

You can also get the total amount of results until a given row by doing the
COUNT(*) query mentioned in this thread, which is pretty much how the
keyset pagination works, but with the condition reversed. See my comment on
how to structure the clause
http://use-the-index-luke.com/no-offset#comment-2994786118

Wout.

On Mon, Nov 14, 2016 at 8:23 AM Mike King  wrote:

> Thanks. In answer to your question a page of results is displayed in a grid
> on the screen. The user selects a row. If they then change the sort order I
> want to show the page that contains the selected row (and reselect it).
> Each row has a unique ID and i need to know the position in the result set
> so I can calculate the page.
>
> Cheers
>
> On Sun, 13 Nov 2016 at 23:26, Igor Tandetnik  wrote:
>
> > On 11/13/2016 6:17 PM, Mike King wrote:
> > > Sorry to reply again so soon. I'm just playing about with your query.
> The
> > > values in Value1 and Value2 are not unique so I don't think your method
> > > would work.
> >
> > Well, in this case, your problem is under-specified. How do you plan to
> > assign a number to a row that's part of a group of rows all sharing the
> > same Value2?
> >
> > One possible approach is to use ID to break ties:
> >
> > select count(*) from Test t1 join Test t2
> > where t2.ID = 1 and
> >(t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID));
> >
> > That's equivalent to "order by Value2, ID" in your temporary table
> > approach.
> > --
> > Igor Tandetnik
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Mike King]

Thanks. In answer to your question a page of results is displayed in a grid
on the screen. The user selects a row. If they then change the sort order I
want to show the page that contains the selected row (and reselect it).
Each row has a unique ID and i need to know the position in the result set
so I can calculate the page.

Cheers

On Sun, 13 Nov 2016 at 23:26, Igor Tandetnik  wrote:

> On 11/13/2016 6:17 PM, Mike King wrote:
> > Sorry to reply again so soon. I'm just playing about with your query. The
> > values in Value1 and Value2 are not unique so I don't think your method
> > would work.
>
> Well, in this case, your problem is under-specified. How do you plan to
> assign a number to a row that's part of a group of rows all sharing the
> same Value2?
>
> One possible approach is to use ID to break ties:
>
> select count(*) from Test t1 join Test t2
> where t2.ID = 1 and
>(t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID));
>
> That's equivalent to "order by Value2, ID" in your temporary table
> approach.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Igor Tandetnik]

On 11/13/2016 6:17 PM, Mike King wrote:

Sorry to reply again so soon. I'm just playing about with your query. The
values in Value1 and Value2 are not unique so I don't think your method
would work.


Well, in this case, your problem is under-specified. How do you plan to 
assign a number to a row that's part of a group of rows all sharing the 
same Value2?


One possible approach is to use ID to break ties:

select count(*) from Test t1 join Test t2
where t2.ID = 1 and
  (t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID));

That's equivalent to "order by Value2, ID" in your temporary table approach.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Mike King]

Sorry to reply again so soon. I'm just playing about with your query. The
values in Value1 and Value2 are not unique so I don't think your method
would work.

Cheers,


On 13 November 2016 at 19:47, Igor Tandetnik  wrote:

> On 11/13/2016 12:29 PM, Mike King wrote:
>
>> So, after some experimentation, I'm using a temporary table to hold the
>> ordered IDs and then getting the rowid of the row with the ID I want. (in
>> this example the list is sorted by Value2 and the selected ID=1):
>>
>> create temporary table TempIDs as select ID from Test order by Value2;
>> select rowid from TempIDs where ID = 1;
>> drop Table TempIDs;
>>
>
> select count(*) from Test
> where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1);
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Mike King]

Wow, that's clever and obvious :) Taking this a stage further, the problem
is that in the real app the user enters a query so there'll be a where
clause to content with as well as the sort order. So, I think I'll use a
CTE to build a list of the selected data and then use your query to select
a record based on the sort criteria.

Thanks for your help

On 13 November 2016 at 19:47, Igor Tandetnik  wrote:

> On 11/13/2016 12:29 PM, Mike King wrote:
>
>> So, after some experimentation, I'm using a temporary table to hold the
>> ordered IDs and then getting the rowid of the row with the ID I want. (in
>> this example the list is sorted by Value2 and the selected ID=1):
>>
>> create temporary table TempIDs as select ID from Test order by Value2;
>> select rowid from TempIDs where ID = 1;
>> drop Table TempIDs;
>>
>
> select count(*) from Test
> where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1);
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Igor Tandetnik]

On 11/13/2016 12:29 PM, Mike King wrote:

So, after some experimentation, I'm using a temporary table to hold the
ordered IDs and then getting the rowid of the row with the ID I want. (in
this example the list is sorted by Value2 and the selected ID=1):

create temporary table TempIDs as select ID from Test order by Value2;
select rowid from TempIDs where ID = 1;
drop Table TempIDs;


select count(*) from Test
where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[Mike King]

Yes spot on.I did come up with a hybrid prototype which used a binary chop
to work out what to read from the database and offset / limit to read the
pages. This was slower than the temp table.

To muddy the waters a bit thecquery is keyed in by the user so to calculate
the page in code I'd have to effectively do the same query in code surely?

Thanks again

Mike

On Sun, 13 Nov 2016 at 18:55, R Smith  wrote:

>
>
> On 2016/11/13 7:29 PM, Mike King wrote:
> > I have a table (test) with 3 columns (ID - auto incrementing, Value1 -
> Text
> > and Value2 - Text). After doing an order by in a select query I'd like to
> > know the row number that contains a particular ID.
> >
> > (The real world use is this: I have an application which displays paged
> > lists of results. If you change the sort order I'd like the application
> to
> > go to the page that contains the current selected ID. To do this I need
> to
> > know what is the position in the sorted list of the ID).
> >
> > So, after some experimentation, I'm using a temporary table to hold the
> > ordered IDs and then getting the rowid of the row with the ID I want. (in
> > this example the list is sorted by Value2 and the selected ID=1):
> >
> > create temporary table TempIDs as select ID from Test order by Value2;
> > select rowid from TempIDs where ID = 1;
> > drop Table TempIDs;
> >
> > I know SQL light doesn't support rownum (like Oracle) but is there any
> way
> > I could simplify this using a CTE so I don't have to create the temp
> table?
> > All I really want is the number of the row with the ID in the sorted
> list.
>
> So if I understand you correct - You use the temporary table with yet
> again a rowid of it's own and populating it using an ordered select from
> your main table, then using this table's rowid to find the list position
> of the item so that you can check its position in the select to know
> where to scroll to so that the first ID shown is the ID that was last
> navigated to. Right?
>
> If so, the best way of doing all this is in your own code. It is some
> magnitudes faster than what you do here. If for some reason you can't do
> it in code, then your temp table is the best solution because nothing
> else can give you row numbers (without some really slow self-joins) and
> there is no guarantees made by any part of SQLite (or SQL in general)
> that a query will pop out a specific row order unless dictated by an
> ORDER BY clause and that happens only AFTER the query rows are produced
> in whatever arbitrary order (so AFTER any row-numbering-scheme could
> have been queried-in).
>
> Perhaps one piece of advice I can offer is that setting the temp-table
> schemata to be created IN MEMORY rather than on disk might speed things
> up a lot (but if you need transactional or ACID integrity maintained you
> will need to switch it to disk again for normal querying, or use a
> different read-only connection with it set to MEMORY for this).
>
>
> Hope that helps.
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

[R Smith]

On 2016/11/13 7:29 PM, Mike King wrote:

I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text
and Value2 - Text). After doing an order by in a select query I'd like to
know the row number that contains a particular ID.

(The real world use is this: I have an application which displays paged
lists of results. If you change the sort order I'd like the application to
go to the page that contains the current selected ID. To do this I need to
know what is the position in the sorted list of the ID).

So, after some experimentation, I'm using a temporary table to hold the
ordered IDs and then getting the rowid of the row with the ID I want. (in
this example the list is sorted by Value2 and the selected ID=1):

create temporary table TempIDs as select ID from Test order by Value2;
select rowid from TempIDs where ID = 1;
drop Table TempIDs;

I know SQL light doesn't support rownum (like Oracle) but is there any way
I could simplify this using a CTE so I don't have to create the temp table?
All I really want is the number of the row with the ID in the sorted list.


So if I understand you correct - You use the temporary table with yet 
again a rowid of it's own and populating it using an ordered select from 
your main table, then using this table's rowid to find the list position 
of the item so that you can check its position in the select to know 
where to scroll to so that the first ID shown is the ID that was last 
navigated to. Right?


If so, the best way of doing all this is in your own code. It is some 
magnitudes faster than what you do here. If for some reason you can't do 
it in code, then your temp table is the best solution because nothing 
else can give you row numbers (without some really slow self-joins) and 
there is no guarantees made by any part of SQLite (or SQL in general) 
that a query will pop out a specific row order unless dictated by an 
ORDER BY clause and that happens only AFTER the query rows are produced 
in whatever arbitrary order (so AFTER any row-numbering-scheme could 
have been queried-in).


Perhaps one piece of advice I can offer is that setting the temp-table 
schemata to be created IN MEMORY rather than on disk might speed things 
up a lot (but if you need transactional or ACID integrity maintained you 
will need to switch it to disk again for normal querying, or use a 
different read-only connection with it set to MEMORY for this).



Hope that helps.
Ryan



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get row number of an ID in sorted results

[Mike King]

I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text
and Value2 - Text). After doing an order by in a select query I'd like to
know the row number that contains a particular ID.

(The real world use is this: I have an application which displays paged
lists of results. If you change the sort order I'd like the application to
go to the page that contains the current selected ID. To do this I need to
know what is the position in the sorted list of the ID).

So, after some experimentation, I'm using a temporary table to hold the
ordered IDs and then getting the rowid of the row with the ID I want. (in
this example the list is sorted by Value2 and the selected ID=1):

create temporary table TempIDs as select ID from Test order by Value2;
select rowid from TempIDs where ID = 1;
drop Table TempIDs;

I know SQL light doesn't support rownum (like Oracle) but is there any way
I could simplify this using a CTE so I don't have to create the temp table?
All I really want is the number of the row with the ID in the sorted list.

Cheers,

Reply
Forward
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users