Hi, everyone,
This looks like a bug to me:
--sql:
CREATE TABLE t(a INTEGER);
INSERT INTO "t" VALUES(1);
INSERT INTO "t" VALUES(2);
INSERT INTO "t" VALUES(3);
INSERT INTO "t" VALUES(4);
SELECT * FROM (
SELECT
a
FROM t
WHERE a<=2
ORDER BY a)
UNION ALL
SELECT * FROM (
SELECT
a
FROM t
WHERE a>2)
LIMIT 1;
--result:
1
3
4
--expected:
1
If I remove ORDER BY from the first SELECT, it gives the expected number of
rows. It looks like LIMIT limits only of the results of querying the first
SELECT statement because of ORDER BY.
Another solution is to use UNION instead of UNION ALL. But I can't use that,
because UNION does not respect ORDER BY in sub-statements (not sure if it's
a correct behavior).
What I'm trying to do is get all names which match the search string. I need
to show all names starting with search string and then show all other
results which contain search string sorting results in each "group".
Something like this:
SELECT * FROM
(
SELECT
name
FROM names
WHERE name LIKE 'a%'
ORDER BY name
)
UNION ALL
SELECT * FROM
(
SELECT
name
FROM all_patients
WHERE name LIKE '%a%' AND
name NOT LIKE 'a%'
ORDER BY name
)
LIMIT 100
In this case LIMIT does not work as expected.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
