On Wed, Dec 15, 2010 at 03:56:06AM -0600, Nicolas Williams wrote:
> SELECT * FROM toy
> WHERE
> a >= (SELECT a FROM toy WHERE id = 6) OR
> (a = (SELECT a FROM toy WHERE id = 6) AND
> b <= (SELECT b FROM toy WHERE id = 6)) OR
> (a = (SELECT a FROM toy WHERE id = 6) AND
> b = (SELECT b FROM toy WHERE id = 6) AND
> c >= (SELECT c FROM toy WHERE id = 6))
> ORDER BY a ASC, b DESC, c ASC LIMIT 1 OFFSET 1;
Oh, and drop the = from the >= and <= tests and you can drop the OFFSET
1 part. Same for the other one. So you get:
SELECT * FROM toy
WHERE
a > (SELECT a FROM toy WHERE id = 6) OR
(a = (SELECT a FROM toy WHERE id = 6) AND
b < (SELECT b FROM toy WHERE id = 6)) OR
(a = (SELECT a FROM toy WHERE id = 6) AND
b = (SELECT b FROM toy WHERE id = 6) AND
c > (SELECT c FROM toy WHERE id = 6))
ORDER BY a ASC, b DESC, c ASC LIMIT 1;
And:
SELECT * FROM toy
WHERE
a < (SELECT a FROM toy WHERE id = 6) OR
(a = (SELECT a FROM toy WHERE id = 6) AND
b > (SELECT b FROM toy WHERE id = 6)) OR
(a = (SELECT a FROM toy WHERE id = 6) AND
b = (SELECT b FROM toy WHERE id = 6) AND
c < (SELECT c FROM toy WHERE id = 6))
ORDER BY a DESC, b ASC, c DESC LIMIT 1;
Both have the same efficient query plan. Drop the index and you'll see
that you get full table scans.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
