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 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] SQLite comes bundled with the Python programming language?
2016-11-13 21:09 GMT+01:00 Simon Slavin : > > On 13 Nov 2016, at 6:00pm, no...@null.net wrote: > >> On Sun Nov 13, 2016 at 10:31:09AM -0700, Keith Medcalf wrote: >> >>> (1) is correct. SQLite is included in the Python distribution. >>> (2) is incorrect. SQLite distribution files do not include Python. >> >> I would say that the sentence as constructed is ambiguous and could be >> interpreted both ways. > > It's correct the way it appears in the documentation: Python distributions > include SQLite. But it's in passive voice, which this generation of grammar > teachers seems to think is bad, for some reason. I am not a native speaker, so I did not get that. But all others (except Firefox) are written with the program as start of the sentence. In the Firefox sentence it is very clear that it is about Firefox using SQLite and not the other way around. Not all users of SQLite are fluent speakers of English, so it would be good to prevent this kind of ambiguity. Kudos for the fast change of the text. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to connect
2016-11-14 7:47 GMT+01:00 jungle boogie : > On 11/13/2016 10:29 PM, Cecil Westerhof wrote: >> >> When going to www.sqlite.org I get: >> Unable to connect >> > > Also happening for me. > > Use https://www.sqlite.org/ until non-https is restored. That works, thanks. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to connect
On 11/13/2016 10:29 PM, Cecil Westerhof wrote: When going to www.sqlite.org I get: Unable to connect Also happening for me. Use https://www.sqlite.org/ until non-https is restored. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to connect
When going to www.sqlite.org I get: Unable to connect -- Cecil Westerhof ___ 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
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
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
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] SQLite comes bundled with the Python programming language?
On 13 Nov 2016, at 6:00pm, no...@null.net wrote: > On Sun Nov 13, 2016 at 10:31:09AM -0700, Keith Medcalf wrote: > >> (1) is correct. SQLite is included in the Python distribution. >> (2) is incorrect. SQLite distribution files do not include Python. > > I would say that the sentence as constructed is ambiguous and could be > interpreted both ways. It's correct the way it appears in the documentation: Python distributions include SQLite. But it's in passive voice, which this generation of grammar teachers seems to think is bad, for some reason. By the way ... support call in Scottish accents: Techie: Technical support, may I help you ? Caller: AR DINNA WANNA BUY A SNAYYK Techie: I'm sorry, Sir ? Caller: AR DINNA WANNA BUY A SNAYYK ! Techie: Erm ... are you having trouble with your computer, Sir ? Caller: Aye ! Yurr progrrram ! Techie: Yes, sir ? Caller: It sayze "Python required to un scipt." 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
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
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
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
Re: [sqlite] SQLite comes bundled with the Python programming language?
On Sun Nov 13, 2016 at 10:31:09AM -0700, Keith Medcalf wrote: > > (1) is correct. SQLite is included in the Python distribution. > (2) is incorrect. SQLite distribution files do not include Python. I would say that the sentence as constructed is ambiguous and could be interpreted both ways. When I first read the statements I matched them the same way Cecil did. Perhaps: SQLite was first distributed with the Python programming language version 2.5. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite comes bundled with the Python programming language?
(1) is correct. SQLite is included in the Python distribution. (2) is incorrect. SQLite distribution files do not include Python. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Cecil Westerhof > Sent: Sunday, 13 November, 2016 08:45 > To: SQLite mailing list > Subject: [sqlite] SQLite comes bundled with the Python programming > language? > > At: > http://www.sqlite.org/famous.html > > I see: > SQLite comes bundled with the Python programming language since Python > 2.5. > > Should that not be: > The Python programming language comes bundled with SQLite since Python > 2.5. > > -- > Cecil Westerhof > ___ > 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] 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 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
[sqlite] SQLite comes bundled with the Python programming language?
At: http://www.sqlite.org/famous.html I see: SQLite comes bundled with the Python programming language since Python 2.5. Should that not be: The Python programming language comes bundled with SQLite since Python 2.5. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Encryption
I am not sure about sqlite2009, but you edit DBs that are encrypted using the encryption in system.data.sqlite. I use SQLITE Expert. It allows you to replace the standard library which does not support encryption to one from the system.data.sqlite that does. It is the interop dll that you replace. Then after entering the password, the encryption and decryption becomes transparent to you. Richard Andersen wrote: In DB Browser for SQlite I can edit the table but I'm not sure if the SQLCipher encryption used here can be made to work with System.Data.SQlite, or how to do if it can. Does anyone know anything about this? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: crash in fts5MultiIterNext()
> > Got a crash in the FTS5 code which only happens on a specific search > > query (int the form of: "ab cd" OR "ab cd" *) > > The crash occurs In fts5MultiIterNext(), on the following line: > > > > pSeg->xNext(p, pSeg, &bNewTerm); > > > > Debugger shows that pSeg is set, but xNext is null. > > > > There are items which match the query. This is with 3.15.1. > > > Are you able to share the database that this crashes when querying? yes, sent you an email with a link. – J ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] add "LINE" to famous sqlite user list
http://www.sqlite.org/famous.html should add https://en.wikipedia.org/wiki/Line_(application) . See https://www.google.com/search?q=Sqlite+Naver+LINE P.S., http://www.sqlite.org/src/wiki?name=Bug+Reports needs to remove the now broken Gmane links. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
