On Mon, Mar 22, 2010 at 12:51 AM, liubin liu <[email protected]> wrote: > > Thanks, I've gotten the point, :) > > and the next question is that how to know the true count after a special > record while getting a special number records. > >
It is not clear at all what you are asking. I am assuming that by "after a special record" you really mean "a WHERE clause" and by "while getting a special number records" you really mean "LIMIT n". Note that SQL doesn't really have a concept of "after a special record." You probably mean all the records > than a condition... hence "WHERE id >= 3". What is not clear is what you mean by "true count." Sqlite is not going to return a false count. If you ask for SELECT Count(*) FROM table WHERE id >= 3 you will get back a single row with one column in it, and that column will tell you how many records in your table satisfy the condition "id > 3". If you want to know how many rows are going to be returned in a result set when you say "LIMIT n", well, you are going to get a maximum of n rows in your result set. If any condition causes the result set to comprise fewer than n rows then you are going to get fewer than n rows. > > P Kishor-3 wrote: >> >> On Sun, Mar 21, 2010 at 9:19 PM, liubin liu <[email protected]> wrote: >>> >>> >>> my application is like below. To get 2 records after a special records. >>> But >>> wrong count, the count is 5: >>> sqlite> >>> sqlite> CREATE TABLE t1 (id INTEGER PRIMARY KEY, data INTEGER); >>> sqlite> INSERT INTO t1 VALUES (3, 999); >>> sqlite> INSERT INTO t1 VALUES (2, 989); >>> sqlite> INSERT INTO t1 VALUES (4, 1009); >>> sqlite> INSERT INTO t1 VALUES (7, 1019); >>> sqlite> INSERT INTO t1 VALUES (9, 1029); >>> sqlite> INSERT INTO t1 VALUES (10, 1039); >>> sqlite> SELECT * FROM t1 WHERE id>=3 LIMIT 2; # right >>> 3|999 >>> 4|1009 >>> sqlite> SELECT COUNT(*) FROM t1 WHERE id>=3 LIMIT 2; # wrong count, >>> why? >>> 5 >>> sqlite> >>> >>> >>> ____________________________________________ >>> >>> I'm confused that the "count(*)" return wrong count. >>> >> >> Read the docs -- "The LIMIT clause places an upper bound on the number >> of rows returned in the result." >> >> >> You are confused about what Count(*) is returning. Count(*) is not >> returning the number of rows in your result set. It is returning the >> number of rows that match your WHERE clause, and that answer itself >> takes up only one row. The LIMIT keyword, on the other hand, is >> limiting the number of rows in your result set. Since the LIMIT clause >> is limiting the answer to 2 rows, but the answer itself consists of >> only one row, the LIMIT clause is pointless. >> >> >> -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
