Geek Answers Handbook

How to get row number in SQLite?

I read a lot of articles on how to use row number in SQLite, but none of them gave me the answer I need. I know how to select a line number using this query:

SELECT (SELECT COUNT() FROM table WHERE title < t.title OR (title = t.title AND id<t.id)) as rowIndex, t.title FROM table AS t ORDER BY t.title;

but if I add COLLATE NOCASE(which I need) at the end of the query, the result will be completely different.

+4
source share
1 answer

Your request contains an error: the alias "ja" is not defined.

Try the following:

SELECT 
  ( SELECT COUNT(*) + 1 
    FROM "table" 
    WHERE title < t.title OR (title = t.title AND id<t.id)
  ) as rowIndex, 
  t.title 
FROM "table" t 
ORDER BY t.title;

However, keep in mind that this subquery design will not scale well. For large datasets, you can create a temporary table and use the ROWID instead (as discussed, for example, here ).

EDIT: COLLATE NOCASE:

CREATE TABLE "table" (id INTEGER, title TEXT COLLATE NOCASE);

INSERT INTO "table" VALUES 
(1, "Book A"), 
(2, "Book b"), 
(3, "Book C"), 
(4, "Book B"), 
(5, "Book a");

:

1|Book A
2|Book a
3|Book b
4|Book B
5|Book C

EDIT:

COLLATE NOCASE, , COLLATE ORDER BY, :

SELECT 
  ( SELECT COUNT(*) + 1 
    FROM "table" 
    WHERE title < t.title COLLATE NOCASE OR (title = t.title COLLATE NOCASE  AND id<t.id)
  ) as rowIndex, 
  t.title 
FROM "table" t 
ORDER BY t.title COLLATE NOCASE;
+4

More articles:


All Articles