SQLite Forum

Can this SQL be simplified?
Login

Can this SQL be simplified?

(1) By Tim Streater (Clothears) on 2021-07-10 12:03:53 [link] [source]

I have this which gives me expected results:

select id from T2 order by mydate limit case when (select count(*) from T2)>(select maxrows from T1) then (select count(*) from T2)-(select maxrows from T1) else 0 end;

This returns the excess oldest rows in T2 (each row has a date stamp) if there are more than maxrows in T2, otherwise no rows.

Is there a way to avoid repeating the selects in the 'then' portion? I tried using 'as' but can't see how to use the column-aliases.

(2) By Domingo (mingodad) on 2021-07-10 12:13:19 in reply to 1 [link] [source]

Hello Tim !

select id
from T2,
    (select count(*) cnt from T2) t2_cnt,
    (select maxrows from T1) max_t1
order by mydate
limit case when t2_cnt.cnt > max_t1.maxrows
    then t2_cnt.cnt - max_t1.maxrows
    else 0 end;
 
Cheers !

(3) By Md Ashraf Rahi (mdashrafrahi086) on 2021-07-10 12:15:55 in reply to 1 [link] [source]

i am trying to like this as: select name from users, (select A_id , count() from event where date<=given date group by A_id order by count() desc ) e where users.A_id=e.a_id limit 0,5… OK

(4) By curmudgeon on 2021-07-10 12:16:30 in reply to 1 [link] [source]

Does

limit max((select count(*) from T2)-(select maxrows from T1), 0)

not work?

(5) By Domingo (mingodad) on 2021-07-10 12:19:56 in reply to 4 [link] [source]

It seems better/simpler than mine !

(6) By Tim Streater (Clothears) on 2021-07-10 13:58:41 in reply to 2 [link] [source]

Not sure I understand this syntax:

    (select count(*) cnt from T2) t2_cnt,
    (select maxrows from T1) max_t1

which seems to be defining new tables, as far as I can see from the later usage such as:

   ... when t2_cnt.cnt > max_t1.maxrows ...

but I can't get that from the SQLite syntax diagrams. In any case, I get this error in the sqlite CLI:

Error: no such column: t2_cnt.cnt

so I'll have a look at max().

(7) By Tim Streater (Clothears) on 2021-07-10 16:09:49 in reply to 4 [link] [source]

Thanks. That allowed me to replace six selects with two.

(8) By Domingo (mingodad) on 2021-07-10 17:26:42 in reply to 4 [link] [source]

It seems that you just found a bug in sqlite !

====
create table T1(id integer primary key, val text);
insert into T1(val) values('1one'), ('1two'), ('1three');

create table T2(id integer primary key, val text);
insert into T2(val) values('2one'), ('2two'), ('2three'), ('2four');

select id, t2_cnt.cnt, max_t1.cnt
from T2,
    (select count(*) as cnt from T2) t2_cnt,
    (select count(*) as cnt from T1) max_t1
order by id

limit case when t2_cnt.cnt > max_t1.cnt
    then t2_cnt.cnt - max_t1.cnt
    else 0 end;
====
Ouput:
====
sqlite3 < "test.sql"
Error: near line 7: no such column: t2_cnt.cnt
====

Removing the "limit":
====
create table T1(id integer primary key, val text);
insert into T1(val) values('1one'), ('1two'), ('1three');

create table T2(id integer primary key, val text);
insert into T2(val) values('2one'), ('2two'), ('2three'), ('2four');

select id, t2_cnt.cnt, max_t1.cnt
from T2,
    (select count(*) as cnt from T2) t2_cnt,
    (select count(*) as cnt from T1) max_t1
order by id;
====
Ouput:
====
sqlite3 < "test.sql"
1|4|3
2|4|3
3|4|3
4|4|3
====

(9) By Domingo (mingodad) on 2021-07-10 17:38:07 in reply to 6 [link] [source]

Not sure I understand this syntax:

    (select count(*) cnt from T2) t2_cnt,
    (select maxrows from T1) max_t1

Yes it's defining new tables it's a shortcut for:

    (select count(*) as cnt from T2) as t2_cnt,
    (select maxrows from T1) as max_t1

(10) By Keith Medcalf (kmedcalf) on 2021-07-10 18:04:00 in reply to 8 [link] [source]

Not a bug.

The expression following LIMIT must be a constant expression.

That is it must be able to be evaluated before the statement to which the limit applies (and must not change during execution) and therefore must not be dependent on data items contained within the query of which it is a part.

Note sure if this is documented but this is how limit works.

(11) By Keith Medcalf (kmedcalf) on 2021-07-10 18:05:34 in reply to 10 [link] [source]

Mutatis mutandis offset.

(12) By Domingo (mingodad) on 2021-07-10 18:19:27 in reply to 10 [link] [source]

You seems to be right, my bad !

Trying in Postgresql gives a better error message:
====
argument of LIMIT must not contain variables
====

(13) By Domingo (mingodad) on 2021-07-10 18:23:51 in reply to 12 [source]

The above was based on just discovered https://extendsclass.com/postgresql-online.html

With this sql:
====
create table T1(id integer primary key, val text);
insert into T1(id, val) values(1, '1one'), (2, '1two'), (3, '1three');

create table T2(id integer primary key, val text);
insert into T2(id, val) values(1, '2one'), (2, '2two'), (3, '2three'), (4, '2four');

select id, t2_cnt.cnt, max_t1.cnt
from T2,
    (select count(*) as cnt from T2) t2_cnt,
    (select count(*) as cnt from T1) max_t1
order by id
limit case when t2_cnt.cnt > max_t1.cnt
    then t2_cnt.cnt - max_t1.cnt
    else 0 end;
====

(14) By anonymous on 2021-07-10 18:27:01 in reply to 1 [link] [source]

    select id from T2
        order by mydate desc
        limit -1
        offset (select maxrows from T1);

(15) By Domingo (mingodad) on 2021-07-11 08:22:35 in reply to 14 [link] [source]

It's a clean query but has the disadvantage of scan all rows of T2.

(16) By anonymous on 2021-07-11 10:50:42 in reply to 15 [link] [source]

So does this fragment, which every other suggestion includes:

    select count(*) from T2

(17.1) By Domingo (mingodad) on 2021-07-11 11:34:00 edited from 17.0 in reply to 16 [link] [source]

You are right !

And I pointed out because other people can see your/our examples and use it in other contexts where paginating using offset for big tables is not a good/wise idea.