As a sidenote, I believe row values were added because of keyset pagination https://use-the-index-luke.com/no-offset. I found them to not be actually useful, so I thought I'd explain here. (copied from my comments on that page (now no longer visible), slightly edited)
I ended up implementing this approach for sqlite since it has the (a,b)<(x,y) operation now, but while doing so I realized that that comparison is not a magical operation that uses the given fields to compare them to the sort order of the query, but instead logically equivalent to `(a < x OR (a = x AND b < y)`. So if you are querying with `ORDER BY a ASC, b DESC`, you have to use `(a > x OR (a = x AND b < y))`, since there is no way to express that with the combined operator. Just wanted to point that out since it surprised me, and since I (wrongly) gleaned from your explanation that the combined operator is necessary to implement keyset pagination. I did some EXPLAINing of "cursor where clauses" and the shortest plans were with clauses of the form: a >= x AND (a != x OR (b <= y AND (b != y OR c > z)) (with ORDER BY a ASC, b DESC, c ASC and x, y, z the last-seen values) A bit annoying to write but the idea is that the DB can scan a in index order, then b if needed, then c if needed. In fact, writing it that way gets me less instructions than when I use the row values. Not sure if that translates in faster queries though. On Fri, Mar 30, 2018 at 7:09 PM David Raymond <david.raym...@tomtom.com> wrote: > https://www.sqlite.org/rowvalue.html section 2.1 for ordering. > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Friday, March 30, 2018 1:06 PM > To: SQLite mailing list > Subject: Re: [sqlite] MIN() and MAX() of set of row values > > On 30 Mar 2018, at 6:04pm, Peter Da Silva <peter.dasi...@flightaware.com> > wrote: > > > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" < > sqlite-users-boun...@mailinglists.sqlite.org on behalf of > slav...@bigfraud.org> wrote: > >> can think of to do it is to devise a metric to turn a pair (a, b) into > one number. > > > > Problem is you can't uniquely order pairs of points. Is (1, 2) greater > or lesser than (2, 1)? > > That's up to you. That's why you are devising the metric. Maybe the > metric is just score == a + b . > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
