On Tue, 02 Jul 2013 10:01:23 +0200 Gabriel Corneanu <gabrielcorne...@gmail.com> 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 wouldn't call it a "workaround"; to me that means some bug or odd behavior than can be awkwardly dealt with. On the contrary, joining the table to itself to produce a rank is simply *using* SQL. The technique has other advantages. The rank can be within a group, not only for the whole set. And the rank can be referenced within the query, permitting joins and further grouping e.g. histograms. All to say: it's an algebra, use it! Codd was right to warn against magical columns. The strange thing is that the warning is still needed. > I wonder if sqlite would make some optimizations out of it, otherwise > it's O(n^2) WHEN having an index for the sort field. Hmm? Just as any join, it's O(n^2) *without* an index: every element has to be compared to every other. A binary index reduces that to O(N log N). Assuming the implementation doesn't take further shortcuts, which it's free to do. Now, there are people out there with truly impressive SQLite databases, but they know who they are and they know how to count the rows as received. For lots of applications, though, O(N log N) is indistinguishable from O(N), and the programming gets done sooner because it's in SQL. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
