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.
P Kishor-3 wrote: > > 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 > > -- View this message in context: http://old.nabble.com/to-get-2-records-after-a-special-records.-But-wrong-count%2C-the-count-is-5-tp27981805p27982625.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
