Thank you very much, Richard, and sorry to disturb you with obvious questions... Really, I need to study SQL language to avoid questions with logical answers...
:) Luciano ----- Original Message ----- From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: "LucaBala" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, October 22, 2003 9:27 AM Subject: [sqlite] Re: Problem > Reply at the bottom. > > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
