4

I've read many articles regarding how to use a row number in SQLite but none of them gave me the answer I need. I know how to select row 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 then the result is completely different.

0101
  • 2,697
  • 4
  • 26
  • 34
  • How does your query look like without the rownum part? – juergen d Oct 20 '13 at 15:36
  • It depends on what kind of row number you want: If you want a number that uniquely identifies a row in the underlying table(s), use ROWID. If you want to number the rows of a query result: Some databases offer a ROWNUM pseuo-field, but sqlite does not AFAIK. A solution using a sub-query has been proposed, for example, [here](http://stackoverflow.com/questions/14023292/how-to-get-rownum-like-column-in-sqlite-iphone). – Fabian Oct 20 '13 at 15:46

2 Answers2

4

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

Try it like this:

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, be aware that this sub-query construction will not scale well. For large datasets you might want to create a temp table and use ROWID instead (as discussed, for example, here).

EDIT: Test with 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");

The query yields:

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

EDIT:

If you do not want to declare the column COLLATE NOCASE, you have to make sure that you use COLLATE in the ORDER BY part as well as in the subquery:

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;
Community
  • 1
  • 1
Fabian
  • 2,822
  • 1
  • 17
  • 22
  • Sorry ja.title was mistake I already corrected it. Your query does work (Same as my) ,but problem is when I edit query "...ORDER BY t.title COLLATE NOCASE;". Do I have to create table using COLLATE NOCASE? – 0101 Oct 21 '13 at 08:07
  • No, you don't have to do that. However, you have to make sure that you use the COLLATE NOCASE in the sub-query consistently. I have added that to the solution, too. – Fabian Oct 21 '13 at 09:46
0
SELECT (SELECT COUNT(*) 
FROM main AS t2 
WHERE t2.col1 < t1.col1) + 
(SELECT COUNT(*) FROM main AS t3 
WHERE t3.col1 = t1.col1 AND t3.col1 < t1.col1) 
AS rowNum, * FROM 
Table_name t1 WHERE rowNum=0 ORDER 
BY t1.col1 ASC
sadegh salehi
  • 11
  • 1
  • 3