LucaBala wrote:
I've a table (COLOURS) with 8 rows (like below), columns CODE (integer primary key) and NAME:
100 BLACK
103 BLUE
104 GREEN
109 GRAY
111 LIGHT GREEN
112 LIGHT BLUE
120 LIGHT RED
121 RED
When I do this command...
SELECT * FROM colours WHERE code >= 100 order by asc limit 3
...the SQLite returns the first 3 rows, but if I do this command...
SELECT count(*) FROM colours WHERE code >= 100 order by asc limit 3
...the SQLite returns...
count(*) = 8
that is the total number rows. Why ?? I think it should be return "count() = 3'", shouldn't it ?
But, if I do this...
SELECT count() FROM colours WHERE code >= 115 order by asc limit 3
the SQLite returns correctly the count() = 2. How do I to get the correct number of rows returned by COUNT() ?
SQLite is behaving as designed. LIMIT restricts the number of rows of output, not the number of rows of input. To achieve the effect you desire, do this:
SELECT count(*) FROM
(SELECT * FROM colours WHERE code>=115 ORDER BY asc LIMIT 3)
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
