CODEWITHSUNDEEP
sqlite
Packing
Packing

Reputation: 17

Row_Number() function not working in sqlite version 3.5.1

I have an SQLite 3.5.1 version.

I would like to know how to use Row_Number() function in SQLite.
I have tried below query in version 3.5.1 but its mot working and its shows an error message:

"21-11-2020 10:11:05: SQL Error: near "(": syntax error <Select *, ROW_NUMBER() OVER(ORDER BY IdNo) AS NoId from LibraryFolder">

Select *, ROW_NUMBER() OVER(ORDER BY IdNo) AS NoId
from LibraryFolder

Upvotes: 0

Views: 649

Answers (1)

forpas
forpas

Reputation: 164204

Your version of SQLite does not support window functions which were introduced in version 3.25.0.

Another way to do what you want is this:

SELECT t.*, 
  (SELECT COUNT(*) FROM LibraryFolder WHERE IdNo <= t.IdNo) AS NoId
FROM LibraryFolder AS t

If IdNo is not unique, then you need additional conditions, including the use of the column rowid to define the order:

SELECT t.*, 
  (
    SELECT COUNT(*) + 1 
    FROM LibraryFolder 
    WHERE IdNo < t.IdNo OR (IdNo = t.IdNo AND ROWID < t.ROWID)
  ) AS NoId
FROM LibraryFolder AS t

Upvotes: 2

Related Questions