Can this SQL be simplified?
(1) By Tim Streater (Clothears) on 2021-07-10 12:03:53 [link]
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]
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 !
(6) By Tim Streater (Clothears) on 2021-07-10 13:58:41 in reply to 2 [link]
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().
(9) By Domingo (mingodad) on 2021-07-10 17:38:07 in reply to 6 [link]
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
(3) By Md Ashraf Rahi (mdashrafrahi086) on 2021-07-10 12:15:55 in reply to 1 [link]
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]
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]
It seems better/simpler than mine !
(7) By Tim Streater (Clothears) on 2021-07-10 16:09:49 in reply to 4 [link]
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]
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 ====
(10) By Keith Medcalf (kmedcalf) on 2021-07-10 18:04:00 in reply to 8 [link]
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]
Mutatis mutandis `offset`.
(12) By Domingo (mingodad) on 2021-07-10 18:19:27 in reply to 10
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 [link]
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]
``` 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]
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]
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]
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.