Re: [sqlite] Limit COUNT

> boun...@sqlite.org] On Behalf Of Bart Smissaert > > That seems to be the answer and after some quick testing it looks it makes it > more efficient as well! > > On Tue, Oct 18, 2011 at 1:36 PM, Kit wrote: > > 2011/10/16 Fabian : > >> How can you

Re: [sqlite] Limit COUNT

That seems to be the answer and after some quick testing it looks it makes it more efficient as well! RBS On Tue, Oct 18, 2011 at 1:36 PM, Kit wrote: > 2011/10/16 Fabian : >> How can you limit a count-query? I tried: >> SELECT COUNT(*) FROM table

Re: [sqlite] Limit COUNT

2011/10/16 Fabian : > How can you limit a count-query? I tried: > SELECT COUNT(*) FROM table LIMIT 5000 SELECT min(COUNT(*),5000) FROM table; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Limit COUNT

> boun...@sqlite.org] On Behalf Of Fabian > Sent: 17 October 2011 15:34 > > > No, I only want to have a capped total available. > > If I would go with Simons solution, I have to read the rows for the first > 100 pages (or whatever the cap is) into a temporary table, just to show the > first page.

Re: [sqlite] Limit COUNT

2011/10/16 Petite Abeille : > On Oct 16, 2011, at 10:39 PM, Kit wrote: >>> select count(*) from (select 1 from table limit 5000) >> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); > > you realize that count( * )  has a very specific meaning, right? > "The count(*)

Re: [sqlite] Limit COUNT

What about this: SELECT CASE count(*) WHEN 5000 THEN 'More than 5000' ELSE 'Less than 5000' END FROM (SELECT ID FROM table ORDER BY whatever LIMIT 5000 OFFSET 25000) Fabian schrieb: > 2011/10/16 Frank Missel > >> What do you want to attain with the count? >> >> > I want to

Re: [sqlite] Limit COUNT

2011/10/16 Frank Missel > > But it sounds a bit like Fabian both wants to have the total number of > records available and at the same time limit the count. > > No, I only want to have a capped total available. If I would go with Simons solution, I have to read the rows for the

Re: [sqlite] Limit COUNT

On Oct 16, 2011, at 10:39 PM, Kit wrote: >> select count(*) from (select 1 from table limit 5000) > > SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); you realize that count( * ) has a very specific meaning, right? "The count(*) function (with no arguments) returns the total number of

Re: [sqlite] Limit COUNT

> select count(*) from (select 1 from table limit 5000) SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

On Oct 16, 2011, at 1:09 PM, Fabian wrote: > How can you limit a count-query? I tried: > > SELECT COUNT(*) FROM table LIMIT 5000 > > But it ignores the LIMIT clause. No it doesn't, it works as advertised. You are falling into the same trap as you did just a couple of threads ago. You need

Re: [sqlite] Limit COUNT

> boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: 16 October 2011 21:53 > > Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's going > to need them eventually for when he displays them), then count how many > rows he got. Yeah, I would go that way also. But it sounds

Re: [sqlite] Limit COUNT

On 10/16/11 14:21, Fabian wrote: I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT) once. Because I'm basicly executing the same query twice

Re: [sqlite] Limit COUNT

On 16 Oct 2011, at 2:50pm, Bart Smissaert wrote: > He is trying to make it more efficient, so stop counting if count > X. > So setting the count after having counted the whole lot won't help. Then he can't use count() because SQLite's implementation of it is not efficient for that. Perhaps he

Re: [sqlite] Limit COUNT

He is trying to make it more efficient, so stop counting if count > X. So setting the count after having counted the whole lot won't help. RBS On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin wrote: > > On 16 Oct 2011, at 1:21pm, Fabian wrote: > >> 2011/10/16 Frank Missel

Re: [sqlite] Limit COUNT

On 16 Oct 2011, at 1:21pm, Fabian wrote: > 2011/10/16 Frank Missel > >> What do you want to attain with the count? > > I want to allow users to paginate through a result set. The pages are > retreived through LIMIT/OFFSET, but to calculate the total number of pages, > I have

Re: [sqlite] Limit COUNT

On 10/16/11 14:21, Fabian wrote: I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT) once. Because I'm basicly executing the same query twice

Re: [sqlite] Limit COUNT

2011/10/16 Frank Missel > > What do you want to attain with the count? > > I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT)

Re: [sqlite] Limit COUNT

oun...@sqlite.org] On Behalf Of Fabian Sent: zondag 16 oktober 2011 13:09 To: General Discussion of SQLite Database Subject: [sqlite] Limit COUNT How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 But it ignores the LIMIT clause. I think the workaround would be count

Re: [sqlite] Limit COUNT

with the count? /Frank > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Fabian > Sent: 16 October 2011 19:09 > To: General Discussion of SQLite Database > Subject: [sqlite] Limit COUNT > > How can yo

[sqlite] Limit COUNT

How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 But it ignores the LIMIT clause. I think the workaround would be counting the results of a sub-query, but I'm trying to understand whats wrong with the syntax above. The goal is to make the engine stop iterating