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

Reply via email to