[sqlite] SQLite - getting number of rows in a database

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?

Not sure if I understand your question, but max(id) won't give you the number of lines at all. For example if you have only one line with id = 13 (let's say you deleted the previous lines), you'll have max(id) = 13 but the number of rows is 1. The correct (and fastest) solution is to use count(). BTW if you wonder why there's a star, it's because you can count lines based on a criteria.

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.

Extension of VolkerK's answer, to make code a little more readable, you can use AS to reference the count, example below:

SELECT COUNT(*) AS c from profile

This makes for much easier reading in some frameworks, for example, i'm using Exponent's (React Native) Sqlite integration, and without the AS statement, the code is pretty ugly.

I got same problem if i understand your question correctly, I want to know the last inserted id after every insert performance in SQLite operation. i tried the following statement:

select * from table_name order by id desc limit 1

The id is the first column and primary key of the table_name, the mentioned statement show me the record with the largest id.

But the premise is u never deleted any row so the numbers of id equal to the numbers of rows.

You can query the actual number of rows with

SELECT Count(*) FROM tblName