I can't say anything about your particular issue with the LIMIT clause, maybe that's a bug. But
> 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). Do you know that SELECT ... FROM (SELECT ... ORDER BY ...) doesn't have to respect your ORDER BY clause? UNION ALL doesn't have to respect your ORDER BY clause either. So the fact that your query behaves exactly that you want it to is a random coincidence and you shouldn't rely on it. It's best for you to execute first select, retrieve all rows and then execute the second select to retrieve the remaining rows you need. Pavel On Wed, Oct 6, 2010 at 12:22 PM, Yuri G <groovy...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
