SQLite - getting number of rows in a database

Question

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?

Answer 1

You can query the actual number of rows with

SELECT Count(*) FROM tblName

see https://www.w3schools.com/sql/sql_count_avg_sum.asp

Answer 2

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

Answer 3

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 4

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.

Answer 5

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.