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

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

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

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

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

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).

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

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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

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

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