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

User · Answer

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.

User · Answer

You can query the actual number of rows withSELECT Count    FROM tblName see https   www w3schools com sql sql count avg sum asp

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sqlite