SQLite - getting number of rows in a database

Question

Hi,

I want to get a number of rows in my table using "max(id)". When it returns NULL - if there are no rows in the table - I want to return 0. And when there are rows I want to return "max(id) + 1".

My rows are being numbered from 0 and autoincreased.

Here is my statement:

SELECT CASE WHEN MAX(id) != NULL THEN (MAX(id) + 1) ELSE 0 END FROM words

But it is always returning me 0. What have I done wrong?

Thank you in advance.

Answer 1

You can query the actual number of rows with

SELECT Count(*) FROM tblName

see http://www.w3schools.com/SQL/sql_func_count.asp

Answer 2

In SQL, NULL = NULL is false, you usually have to use IS NULL:

SELECT CASE WHEN MAX(id) IS NULL THEN 0 ELSE (MAX(id) + 1) END FROM words

But, if you want the number of rows, you should just use count(id) since your solution will give 10 if your rows are (0,1,3,5,9) where it should give 5.

If you can guarantee you will always ids from 0 to N, max(id)+1 may be faster depending on the index implementation (it may be faster to traverse the right side of a balanced tree rather than traversing the whole tree, counting.

But that's very implementation-specific and I would advise against relying on it, not least because it locks your performance to a specific DBMS.

Answer 3

If you want to use the MAX(id) instead of the count, after reading the comments from Pax then the following SQL will give you what you want

SELECT COALESCE(MAX(id)+1, 0) FROM words