At 05:49 04/07/2013, you wrote:
´¯¯¯
But in the bigger context of this discussion, I think SQLite might
consider a row_number() function, or (my personal preference) some
type of virtual column, such as "_row_number_". I suggest that term,
since that's what Oracle, MS SQL Server, and PostgreSQL use. I
prefer the idea of a virtual column because, unlike a function, it is
difficult to misuse a virtual column in some other context. If it
can only be expressed in a result set selector (the SELECT clause),
it will only work there.
While I understand your arguments in favor of this feature and I
believe it could be used without adding much bloat, allow me to be the
devil's advocate for a while.
-- There are other ways of doing this in SQL.
Umm, exactly?
You will certainly agree that a ranking feature is not going to be used
in most queries. It only serves in relatively rare cases.
OTOH as your example shows the usefulness of a ranking function is
merely limited to small result sets where ranking has a meaning for
humans. What I mean here is that you're more likely to be interessed in
the top 10 <something> than in the <something> having ranks between
123456779 and 123456789. Top 10 or 100 or 1000 but never top billion.
Unless you have an exceptional use case ranked result sets are more
likely to be small.
At this point why not use one of the SQL way to do it, using a temp
table or view to get the new rowid do the ranking automagically?
Using memory as temp store this will add little overhead to such rarely
used queries. Even where temp store is set to disk and carved in stone
by compile-time option the overhead should be minimal due to the
typical small number of rows involved in the result set.
I don't buy your argument that the rank should in theory be an explicit
part of the result set, contrary to autoincrement rowid bumping from 1.
So if you want a view that provides
any type of ordering, you need to be able to provide a rank column as
part of the view result.
You never need to query for the top 10 sales region every tenth of a
second (or you're a burnt out sales manager).
On the contrary I'd say that a rank is the result of some sort
depending on values spread in joined tables. When those values get
inserted or otherwise updated, the rank is likely to change. Keeping it
as an explicit column then means significant overhead.
Or did I misunderstand your statements on this point?
-- You can just do it in code.
Well sure.
Not so sure: for instance using the CLI doesn't allow any kind of
"programming".
--
<mailto:j...@q-e-d.org>j...@antichoc.net
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
