Date: Fri, 05 Jul 2013 02:04:04 +0200 From: Olaf Schmidt <n...@vbrichclient.com> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) Message-ID: <kr52ig$e71$1...@ger.gmane.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
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 ... Oh, so convenience is now bad suddenly? [...] Because there's a lot of things one can use that for - especially when you consider the concept of disconnected Recordsets, which can be passed around in an application, or across thread- or machine-boundaries - generic container-classes, which can be bound to grids - or used as the datasource for parts in a Report ... in any of those cases such a "directly contained info" can be useful, when it's already "there in the returned set-object as a calculated column-value". [...] ==== J. Merrill's thoughts below ==== 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. The "row number" value as commonly described in this thread is completely useless except in the context of one specific execution of a particular SQL statement. You would need to use the table's primary key value to do any updates to the original table -- possibly with the values of some/all non-PK values to avoid updating rows that had been changed by other sessions/users after the initial SELECT. You would definitely not want to relate one recordset to another using the "row number" value, because executing the exact same SQL statement 1 second later could return a completely different "row number" value for every primary key. (Pretty much the ONLY data in such a recordset that would never be useful to relate to another recordset is this kind of ephemeral "row number" value.) Personally, I'd like to see an implementation of the "row_number() OVER (ORDER by xxx)" syntax, and if possible also the inclusion of the "PARTITION BY" clause within the OVER () expression. The PARTITION BY syntax lets you get row numbers within groups. Doing that would add a feature to SQLite that's in a other SQL implementations, and it does not violate relational purity because the OVER clause keeps the ROW_NUMBER() function from being non-deterministic when the overall statement has no ORDER BY. Having some other syntax that's unique to SQLite does not seem right. It would not offend me if the initial implementation of this feature were limited in that PARTITION BY is not implemented and requiring the ORDER BY clause for a column defined by "ROW_NUMBER() OVER (ORDER by xxx)" had to match the ORDER BY of the result set. That would give people the feature that seems to be so desired without a SQLite-specific syntax, and would leave open the possibility of having a more complete implementation later. Note that the ROW_NUMBER() OVER syntax defines the row number without regard to the ordering of the result set. You could have multiple columns defined with ROW_NUMBER() OVER, each with a different ORDER BY -- for example you could have ROW_NUMBER() OVER (ORDER BY sales_total desc) while the result set has its ORDER BY be something else (like salesperson name). My $0.04. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
