Как получить номер строки в SQLite?

Я прочитал много статей о том, как использовать номер строки в SQLite, но ни одна из них не дала мне нужного ответа. Я знаю, как выбрать номер строки, используя этот запрос: 

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;

Но если я добавлю COLLATE NOCASE (что мне нужно) в конце запроса, то результат будет совершенно другим.

1 4
sqlite row-number

1 ответ:

Ваш запрос содержит ошибку: псевдоним " ja " не определен.

Попробуйте сделать так: 

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;
Однако имейте в виду, что эта конструкция подзапроса не будет хорошо масштабироваться. Для больших наборов данных вы можете создать временную таблицу и использовать вместо нее ROWID (как описано, например, здесь ).

Редактировать: Тест с 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

Редактировать:

Если вы не хотите объявлять столбец COLLATE NOCASE, вы должны убедиться, что вы используете COLLATE в порядке по частям, а также в подзапросе: 

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