Re: [sqlite] MIN() and MAX() of set of row values
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 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 > 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
Re: [sqlite] MIN() and MAX() of set of row values
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 wrote: > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" > 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
Re: [sqlite] MIN() and MAX() of set of row values
On 3/30/2018 1:04 PM, Peter Da Silva wrote: On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" 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. Yes you can. Is (1, 2) greater or lesser than (2, 1)? "select (1, 2) < (2, 1)" says lesser. For further discussion, see https://www.sqlite.org/rowvalue.html#row_value_comparisons -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
Why not something simpler, like select * from T order by a, b limit 1; --for the min, select * from T order by a desc, b desc limit 1; --for the max? select (select a, b from T order by a, b limit 1) = (1, 2) as ok; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Brand Sent: Friday, March 30, 2018 10:48 AM To: SQLite mailing list Subject: [sqlite] MIN() and MAX() of set of row values Hi, Row values make life easier in so many ways, but I was just wondering if there is (or should be or could be) a way to use aggregate MIN() and MAX() on a set of row values. Mark CREATE TABLE T (a, b); INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); SELECT MIN((a,b)) = (1, 2) ok FROM T; --Error: near line 4: row value misused SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; --Error: near line 7: row value misused SELECT (1, 2) = MIN(SELECT a, b FROM T); --Error: near line 10: near "SELECT": syntax error --ok SELECT T.* FROM T LEFT JOIN T S ON (S.a, S.b) < (T.a, T.b) WHERE S.a IS NULL; --ok SELECT (T.a, T.b) = (1, 2) ok FROM T LEFT JOIN T S ON (S.a, S.b) < (T.a, T.b) WHERE S.a IS NULL; ___ 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
Re: [sqlite] MIN() and MAX() of set of row values
On 30 Mar 2018, at 6:04pm, Peter Da Silva wrote: > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" > 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
Re: [sqlite] MIN() and MAX() of set of row values
On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" 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)? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
On 30 Mar 2018, at 5:55pm, Igor Tandetnik wrote: > I don't think that's what the OP had in mind. They didn't want the smaller of > a and b for each row, but rather the lexicographically smallest (a, b) pair > among all rows. > > Row values support less-than comparison, so it kind of makes sense to expect > MIN to work on them, too. Ah. Okay, I get it. The only way I can think of to do it is to devise a metric to turn a pair (a, b) into one number. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
On 3/30/2018 12:10 PM, Simon Slavin wrote: On 30 Mar 2018, at 3:48pm, Mark Brand wrote: SELECT MIN((a,b)) = (1, 2) ok FROM T; --Error: near line 4: row value misused SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; --Error: near line 7: row value misused SELECT (1, 2) = MIN(SELECT a, b FROM T); --Error: near line 10: near "SELECT": syntax error You got very close to the right syntax. SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE T (a, b); sqlite> INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); sqlite> SELECT min(a,b) FROM T; 1 1 2 I don't think that's what the OP had in mind. They didn't want the smaller of a and b for each row, but rather the lexicographically smallest (a, b) pair among all rows. Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
On 30 Mar 2018, at 3:48pm, Mark Brand wrote: > SELECT MIN((a,b)) = (1, 2) ok FROM T; > --Error: near line 4: row value misused > > SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; > --Error: near line 7: row value misused > > SELECT (1, 2) = MIN(SELECT a, b FROM T); > --Error: near line 10: near "SELECT": syntax error You got very close to the right syntax. SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE T (a, b); sqlite> INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); sqlite> SELECT min(a,b) FROM T; 1 1 2 Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MIN() and MAX() of set of row values
Hi, Row values make life easier in so many ways, but I was just wondering if there is (or should be or could be) a way to use aggregate MIN() and MAX() on a set of row values. Mark CREATE TABLE T (a, b); INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); SELECT MIN((a,b)) = (1, 2) ok FROM T; --Error: near line 4: row value misused SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; --Error: near line 7: row value misused SELECT (1, 2) = MIN(SELECT a, b FROM T); --Error: near line 10: near "SELECT": syntax error --ok SELECT T.* FROM T LEFT JOIN T S ON (S.a, S.b) < (T.a, T.b) WHERE S.a IS NULL; --ok SELECT (T.a, T.b) = (1, 2) ok FROM T LEFT JOIN T S ON (S.a, S.b) < (T.a, T.b) WHERE S.a IS NULL; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
