On Sun, Mar 21, 2010 at 9:19 PM, liubin liu <7101...@sina.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
