Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the wall: > "select *" is shorthand for "all columns". You'll note that what's > returned isn't some kind of special '*' column, but all columns. The > order in which the columns are returned isn't meaningful because the >

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

Bravo Alex !! From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Alex Bowden [a...@designlifecycle.com] Sent: Monday, July 01, 2013 12:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is there a way

Re: [sqlite] Another 2 questions about SQLite

On 7/1/2013 11:30 PM, James K. Lowden wrote: Restricting ourselves to one process, I can think of two ways that might go: 1. With two connections, one connection or the other will wait. SELECT will return 0 or 10 rows. Yes, between two connection, normal transaction isolation rules apply,

Re: [sqlite] Another 2 questions about SQLite

On Mon, 01 Jul 2013 23:00:27 -0400 Igor Tandetnik wrote: > On 7/1/2013 10:33 PM, James K. Lowden wrote: > > Igor Tandetnik wrote: > >> If you change data that a live SELECT statement is iterating over, > >> the outcome is unpredictable. It may appear to

Re: [sqlite] Another 2 questions about SQLite

On 7/1/2013 10:33 PM, James K. Lowden wrote: On Sun, 30 Jun 2013 23:27:23 -0400 Igor Tandetnik wrote: If you change data that a live SELECT statement is iterating over, the outcome is unpredictable. It may appear to work, it may skip rows, it may return some rows more than

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Mon, 01 Jul 2013 13:10:59 +0200 Gabriel Corneanu wrote: > I also needed this for some special update queries. > Without many details, it was some kind of "insert into xxx select > , otherfields from source order by ". > For this case there is a workaround,

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Mon, 1 Jul 2013 14:22:53 +0300 "Tony Papadimitriou" wrote: > Just because you can select something doesn't mean you have to be > able to sort by it. Yes, it does. > Can you sort by * (select * by table sort by *)? You have confused syntax with semantics. "select *"

Re: [sqlite] Another 2 questions about SQLite

On Sun, 30 Jun 2013 23:27:23 -0400 Igor Tandetnik wrote: > On 6/30/2013 11:13 PM, Igor Korot wrote: > > Well I will use another statement variable as in the sample code. > > My questions was: if I call delete on the record that was just > > retrieved in another query will

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

Yes, you can sort by * > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Tony Papadimitriou > Sent: Monday, 1 July, 2013 05:23 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Is there a way to return

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

rownumber = 0 do while ThereAreRows: sqlite3_step ... rownumber += 1 ... pretty simple eh? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Tony Papadimitriou > Sent: Monday, 1 July, 2013 03:33 > To: General

Re: [sqlite] assertion in sqlite 3.7.17 for rather simple SELECT query

On Mon, Jul 1, 2013 at 9:20 AM, Jens Miltner wrote: > I recently updated our SQLite source to SQLite source distribution 3.7.17. > Since then, we get assertions when running one of our larger queries. I > have stripped down the query to a bare minimum query that produces the same >

Re: [sqlite] GUI for SQLite (nobre)

SQLite Expert (Windows) is good and I've been using it for the last 2 years but unfortunately it only does (Windows). Although, it also runs good in WINE on Linux. I've recently started using Valentina Studio (Linux/Windows/Mac) because I need to also run on native Linux, and if I get rich I'll

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

> OK, you don't agree. Your opinion! (That doesn't make you right, though!) If you approach the government france and explain that you're not really very good at French, but if they made the following list of Chinese language inclusions into French, then it would make it easier for you to

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

OK, you don't agree. Your opinion! (That doesn't make you right, though!) I'm sure there will be a SQL engine somewhere that will do it for you. We're talking about SQLite here, aren't we? If some other database can do it, then you should also consider that it may also be able to do what

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I agree with you completely! It has to be relatively simple to implement. And certainly it adds a very important missing functionality (for those who can see it, that is). Some kind of internal cursor has to iterate to return rows to the user after the query is completely executed. This can

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

> Please! Just because you can select something doesn't mean you have to be > able to sort by it. There are a small number of exceptions, each of which is a bodge. But some bodges are worth the impact. > Can you sort by * * is a very useful and largely harmless bodge. There is

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On 1 Jul 2013, at 2:54pm, Gabriel Corneanu wrote: > While I agree non-standard features should be kept to minimum possible, I > needed this so often that I simply think there is a larger potential benefit. > Another example: I don't do web design, but I remember some

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

Hi Simon, No problem, but I don't understand what has this to do with multi - processing / users?? (the query program has a lock on the table anyway). The counter can be either reused (see the other "LIMIT" discussion) or it needs a different implementation. As I don't think that the

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

Hi, My opinion is a little different: a) of course the compiler needs to change the query program (only if "nrow" is requested/used) b) I don't know the internals, but I just can't believe that such a value could not be exported somehow c) I understand it would be non-standard; however there

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On 1 Jul 2013, at 2:00pm, Gabriel Corneanu wrote: > As I wrote it's easy to do when you control the loop. > Sometimes there is either no explicit loop (direct sql, no programming code) > or the loop is out of your reach (3rd party library). Sorry, I posted that

[sqlite] assertion in sqlite 3.7.17 for rather simple SELECT query

I recently updated our SQLite source to SQLite source distribution 3.7.17. Since then, we get assertions when running one of our larger queries. I have stripped down the query to a bare minimum query that produces the same assertion: The following rather simple SELECT query gives me an

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

The problem is a) you must have a limit clause for the register to even be tallied b) the register is not part of the result set c) it would be a very incompatible change to SQL syntax d) there are other ways of achieving the same result, either in the caller or in a user written function

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

Simon, As I wrote it's easy to do when you control the loop. Sometimes there is either no explicit loop (direct sql, no programming code) or the loop is out of your reach (3rd party library). Gabriel ___ sqlite-users mailing list

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

Then this register value is exactly the needed result. There is also the other syntax, "limit n, m"; you have to skip somehow "m" rows. Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Getting Constraints Details

On Mon, 1 Jul 2013 07:23:54 +, Vijay Khurdiya wrote: >How to get details of constraints associated with Data in SQLite3. > >Ex : I want to find out DEFAULT constraints value associated with data. Some of that info is available in the results of PRAGMA

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On 1 Jul 2013, at 10:33am, Tony Papadimitriou wrote: > Is there a function (or method), e.g., row(), to return the sequence number > of the selected row? This is not the same as ROWID. row() should give a > sequence number always starting from 1 up the to the number of rows

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

For this application I would suggest: DROP TABLE IF EXISTS query_results; CREATE TEMP TABLE query_results (...); INSERT INTO query_results SELECT ORDER BY LIMIT ; SELECT rowid,* from query_results; DROP TABLE IF EXISTS query_results; SQLite implicitly assigns numerical ascending rowids to a

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

Thanks! (At least some understand what I mean!) And people giving examples of how it can be done in C (or Python, for me) or whatever language miss the point. We're not talking how it can be programmatically. This is easy!!! How does one do it via pure SQL is the real question. As for

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

Please! Just because you can select something doesn't mean you have to be able to sort by it. Can you sort by * (select * by table sort by *)? So, why make it sound like I don't know what I'm talking about? -Original Message- From: Alex Bowden Sent: Monday, July 01, 2013 2:07 PM

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

Hi, Where the "row number in the answer set" does come in useful (or at least where I've used it a number of itmes) is when populating a table with the results of a select and including this value as another column in that table. "build a table with the top 10 selling items over the last week"

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I also needed this for some special update queries. Without many details, it was some kind of "insert into xxx select , otherfields from source order by ". For this case there is a workaround, selecting first into a temporary table with auto generated rowid and using it afterwards for

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

> This would not be something you would sort by. And what if I do? > It should be assigned a value only during final 'display' of the query after > all 'sorts' of operations are done with. Oh great. So the user is supposed to understand the implementation, in order to understand what the

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I can't wait to try order by row_number desc LOL - But done already: PRAGMA reverse_unordered_selects = 1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I can't imagine the usefulness of this. The SQLite will have to run a query to find it, same as you need to, consider some standard sqlite code: sqlite prepare n=0 while sqlite_next=SQLITE_OK do { // do something with data inc(n) } The physical index of the row inside the query will always

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

This would not be something you would sort by. It should be assigned a value only during final 'display' of the query after all 'sorts' of operations are done with. -Original Message- From: Alex Bowden Sent: Monday, July 01, 2013 12:46 PM To: General Discussion of SQLite Database

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I can't wait to try order by row_number desc On 1 Jul 2013, at 10:33, Tony Papadimitriou wrote: > Is there a function (or method), e.g., row(), to return the sequence number > of the selected row? This is not the same as ROWID. row() should give a > sequence number

[sqlite] Is there a way to return the row number? (NOT the rowid)

Is there a function (or method), e.g., row(), to return the sequence number of the selected row? This is not the same as ROWID. row() should give a sequence number always starting from 1 up the to the number of rows returned by the view/select etc. If not, then please add to the wish list.

Re: [sqlite] Another 2 questions about SQLite

I wish. I need to remove those records from another table as well. That's why I need to retrieve playerid first. So once again: will the delete affect the outer looping SQLite statement? I hate to be the one asking the obvious questions, but why are you not using a trigger or or a foreign key

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.87.0 have begun...

If you have any issues with the current code, please report them via this mailing list (and/or by creating a ticket on "https://system.data.sqlite.org/;) prior to next Monday, July 8th. The support for implementing virtual tables in managed code is completely new and merits special attention.

Re: [sqlite] Getting Constraints Details

Vijay Khurdiya wrote: > How to get details of constraints associated with Data in SQLite3. There is no API to get individual properties; you have to look up the original SQL statement: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable' > This e-mail and any files

[sqlite] Getting Constraints Details

How to get details of constraints associated with Data in SQLite3. Ex : I want to find out DEFAULT constraints value associated with data. This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.