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

On 8 Jul 2013, at 5:51am, James K. Lowden wrote: > Your test doesn't show that the above *query* is inefficient, but that > SQLite executes it inefficiently. The query can be executed > efficiently; whether or not SQLite does so is a design choice. One problem with

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

On Fri, 05 Jul 2013 02:19:08 +0200 Olaf Schmidt wrote: > Create Table T (ID Integer Primary Key, Item Text) > > Select Count(Lesser.ID), T.ID, T.Item From T As T > Left Outer Join T As Lesser > On T.ID > Lesser.ID > Group By T.ID > Order By

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

On Fri, 05 Jul 2013 12:38:37 +0200 Gabriel Corneanu wrote: > About complexity: I'm not sure it's NlogN; for each N you need to > count N-1 columns, that's N^2 IMO. You're right if the data aren't sorted. If the data are sorted, to *find* the largest value smaller

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

On Sun, Jul 7, 2013 at 3:31 PM, Alex Bowden wrote: > > State why you don't > > like it and move on. Don't contribute any code that might address the > idea. > > The rest? It is not useful. > > It is useful. It help stops people who don't understand the concept of >

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

> State why you don't > like it and move on. Don't contribute any code that might address the idea. > The rest? It is not useful. It is useful. It help stops people who don't understand the concept of relational, screwing up the system. You just disagree with that. On 7 Jul 2013, at 22:24,

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

On Sun, Jul 7, 2013 at 11:34 AM, Keith Medcalf wrote: > > Despite the long diatribes you have not indicated a single case in which > the set ordinal of the row would be of any use whatsoever. > > For 40 years we have gotten on without it, so yes, it is only for you > newbies

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

Despite the long diatribes you have not indicated a single case in which the set ordinal of the row would be of any use whatsoever. For 40 years we have gotten on without it, so yes, it is only for you newbies that somehow think there is a use for it, and for 40 years no one has ever come

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

Am 06.07.2013 18:58, schrieb j.merrill- There are reasons to want what is in other SQL implementations implemented with "row_number() OVER (ORDER BY ...)" but "disconnected Recordsets" or for data "passed across thread- or machine-boundaries" are definitely NOT valid reasons. Since they are

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

Am 06.07.2013 19:25, schrieb Keith Medcalf: Date: Fri, 05 Jul 2013 02:04:04 +0200 From: Olaf Schmidt To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) Message-ID: Content-Type:

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

>Date: Fri, 05 Jul 2013 02:04:04 +0200 > >From: Olaf Schmidt >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Is there a way to return the row number? (NOT >therowid) >Message-ID: >Content-Type: text/plain; charset=ISO-8859-1;

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

Date: Fri, 05 Jul 2013 02:04:04 +0200 From: Olaf Schmidt To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) Message-ID: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Am

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

Am 04.07.2013 20:36, schrieb James K. Lowden: Yes, and there's your O(N log N): N for the SCAN and log(N) for the SEARCH. To process 1,000,000 rows takes 1,000,000 accesses. To produce the rank requires roughly 20 searches per row (given an appropriate index), or 20,000,000 total accesses.

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

Am 03.07.2013 14:50, schrieb Keith Medcalf: Given all that, I will NEVER use the pure sql (if I can use any other solution). given that the "ordinal in the result set" is a fallacious concept being created for the convenience of an application > program which cannot deal with sets properly

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

Hi James, About complexity: I'm not sure it's NlogN; for each N you need to count N-1 columns, that's N^2 IMO. And you have an EXTRA temporary B-TREE? Doesn't it matter?? Although I don't really understand why, it has an index on it. My original concern is indeed simplicity and efficiency.

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

On Wed, 03 Jul 2013 11:11:29 +0200 Gabriel Corneanu wrote: > I reply from the web and I can't easily quote. Acknowledged, but it does make the thread more difficult to read. :-/ > I don't really want to argue whether it's a workaround or not. I > understand

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

Keith, I find this a little arrogant. I could say exactly the opposite, not caring about efficiency is ... (introduce whatever words you like). Why is this ranking addressed here? http://www.schemamania.org/sql/#rank.rows Why do you need "order by" in sql but may not ask the ranking having a

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

> Given all that, I will NEVER use the pure sql (if I can use any other > solution). given that the "ordinal in the result set" is a fallacious concept being created for the convenience of an application program which cannot deal with sets properly (or more likely a programmer who does not

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

James, I reply from the web and I can't easily quote. I don't really want to argue whether it's a workaround or not. I understand perfectly that's valid standard sql. However please accept that the given sql is quite complex; you have to duplicate in the join clause the ordering... About

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

On Tue, 02 Jul 2013 10:01:23 +0200 Gabriel Corneanu wrote: > > Ranking the rows requires nothing more than joining the table to > > itself. > Indeed, that's the case. However, I can't imagine this to be > efficient. It's just a pure sql workaround to a counter. I

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

James, Indeed, that's the case. However, I can't imagine this to be efficient. It's just a pure sql workaround to a counter. The only advantage is, it is standard sql and should work with any engine. I wonder if sqlite would make some optimizations out of it, otherwise it's O(n^2) WHEN

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

Simon, Of course the rowid is THE field to use to uniquely identify a row (for edit, delete, etc...). I am talking about some ranking a result set. The ranking can be used for displaying (in HTML or a desktop GUI) or as source for insert/update sql. Of course in code you can have your own

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? (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] 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? (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 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

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