[sqlite] union all with limit
Trying to run this SQL: select patient_id from table1 where age = 50 limit 6 union all select patient_id from table1 where age = 60 limit 4 But it fails due to the limit clause before the union. Would there be a way round this? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Hello BS> select BS> patient_id BS> from BS> table1 BS> where BS> age = 50 BS> limit 6 BS> union all BS> select BS> patient_id BS> from BS> table1 BS> where BS> age = 60 BS> limit 4 You might want to wrap the two selects with limits inside subqueries: select patientID from ( select patientID from table1 where age = 50 limit 6 ) union all select patientID from ( select patientID from table1 where age = 60 limit 4 ); Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Thanks, I tried that and it gives no error, but only gives the first lot, not the bit after the union all. RBS On Wed, Nov 24, 2010 at 10:27 AM, Swithun Crowe wrote: > Hello > > BS> select > BS> patient_id > BS> from > BS> table1 > BS> where > BS> age = 50 > BS> limit 6 > BS> union all > BS> select > BS> patient_id > BS> from > BS> table1 > BS> where > BS> age = 60 > BS> limit 4 > > You might want to wrap the two selects with limits inside subqueries: > > select patientID > from ( > select patientID > from table1 > where age = 50 > limit 6 > ) > union all > select patientID > from ( > select patientID > from table1 > where age = 60 > limit 4 > ); > > Swithun. > ___ > 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
Re: [sqlite] union all with limit
Hello BS> Thanks, I tried that and it gives no error, but only gives the first BS> lot, not the bit after the union all. Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). In your real data and query, can a patient have more than one age? If not, then UNION and UNION ALL would produce the same results. sqlite> create table table1 (patientID int, age int); sqlite> insert into table1 (patientID, age) values (1, 50); sqlite> insert into table1 (patientID, age) values (2, 50); sqlite> insert into table1 (patientID, age) values (3, 50); sqlite> insert into table1 (patientID, age) values (4, 50); sqlite> insert into table1 (patientID, age) values (5, 50); sqlite> insert into table1 (patientID, age) values (6, 50); sqlite> insert into table1 (patientID, age) values (7, 50); sqlite> insert into table1 (patientID, age) values (8, 60); sqlite> insert into table1 (patientID, age) values (9, 60); sqlite> insert into table1 (patientID, age) values (10, 60); sqlite> insert into table1 (patientID, age) values (11, 60); sqlite> insert into table1 (patientID, age) values (12, 60); This seems to work: select patientID from table1 where patientID in (select patientID from table1 where age=50 limit 6) union all select patientID from table1 where patientID in(select patientID from table1 where age=60 limit 4); 1 2 3 4 5 6 8 9 10 11 Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Thanks, that one works indeed and will use that. RBS On Wed, Nov 24, 2010 at 11:08 AM, Swithun Crowe wrote: > Hello > > BS> Thanks, I tried that and it gives no error, but only gives the first > BS> lot, not the bit after the union all. > > Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION > ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). > In your real data and query, can a patient have more than one age? If not, > then UNION and UNION ALL would produce the same results. > > sqlite> create table table1 (patientID int, age int); > sqlite> insert into table1 (patientID, age) values (1, 50); > sqlite> insert into table1 (patientID, age) values (2, 50); > sqlite> insert into table1 (patientID, age) values (3, 50); > sqlite> insert into table1 (patientID, age) values (4, 50); > sqlite> insert into table1 (patientID, age) values (5, 50); > sqlite> insert into table1 (patientID, age) values (6, 50); > sqlite> insert into table1 (patientID, age) values (7, 50); > sqlite> insert into table1 (patientID, age) values (8, 60); > sqlite> insert into table1 (patientID, age) values (9, 60); > sqlite> insert into table1 (patientID, age) values (10, 60); > sqlite> insert into table1 (patientID, age) values (11, 60); > sqlite> insert into table1 (patientID, age) values (12, 60); > > This seems to work: > > select patientID > from table1 > where patientID in (select patientID from table1 where age=50 limit 6) > union all > select patientID > from table1 > where patientID in(select patientID from table1 where age=60 limit 4); > > 1 > 2 > 3 > 4 > 5 > 6 > 8 > 9 > 10 > 11 > > Swithun. > ___ > 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
Re: [sqlite] union all with limit
On 24 November 2010 11:08, Swithun Crowe wrote: > Hello > > BS> Thanks, I tried that and it gives no error, but only gives the first > BS> lot, not the bit after the union all. > > Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION > ALL, when it is buried in a subquery. It doesn't in version 3.7.0.1 sqlite> select sqlite_version(); 3.7.0.1 sqlite> sqlite> create table table1 (patientID int, age int); sqlite> insert into table1 (patientID, age) values (1, 50); sqlite> insert into table1 (patientID, age) values (2, 50); sqlite> insert into table1 (patientID, age) values (3, 50); sqlite> insert into table1 (patientID, age) values (4, 50); sqlite> insert into table1 (patientID, age) values (5, 50); sqlite> insert into table1 (patientID, age) values (6, 50); sqlite> insert into table1 (patientID, age) values (7, 50); sqlite> insert into table1 (patientID, age) values (8, 60); sqlite> insert into table1 (patientID, age) values (9, 60); sqlite> insert into table1 (patientID, age) values (10, 60); sqlite> insert into table1 (patientID, age) values (11, 60); sqlite> insert into table1 (patientID, age) values (12, 60); sqlite> sqlite> select patientID ...> from ( ...> select patientID ...> from table1 ...> where age = 50 ...> limit 6 ...> ) ...> union all ...> select patientID ...> from ( ...> select patientID ...> from table1 ...> where age = 60 ...> limit 4 ...> ); 1 2 3 4 5 6 8 9 10 11 sqlite> > > Swithun. Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users