[sqlite] union all with limit

2010-11-24 Thread Bart Smissaert 
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

2010-11-24 Thread Swithun Crowe 
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

2010-11-24 Thread Bart Smissaert 
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

2010-11-24 Thread Swithun Crowe 
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

2010-11-24 Thread Bart Smissaert 
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

2010-11-24 Thread Simon Davies 
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