How to use ROW_NUMBER in sqlite

Question

Here is my query given below.

select * from data where value = "yes"; 

My id is auto increment and below there is result of given query.

id || value  1  ||   yes 3  ||   yes 4  ||   yes 6  ||   yes 9  ||   yes 

How to use ROW_NUMBER in sqlite? So that i can get result which is given below.

NoId || value  1    ||   yes 2    ||   yes 3    ||   yes 4    ||   yes 5    ||   yes 

ROW_NUMBER AS NoId.

Answer 1

SQLite Release 3.25.0 will add support for window functions

2018-09-15 (3.25.0)

1. Add support for window functions

---

Window Functions :

A window function is a special SQL function where the input values are taken from a "window" of one or more rows in the results set of a SELECT statement.

SQLite supports the following 11 built-in window functions:

row_number()

The number of the row within the current partition. Rows are numbered starting from 1 in the order defined by the ORDER BY clause in the window definition, or in arbitrary order otherwise.

So your query could be rewritten as:

select *, ROW_NUMBER() OVER(ORDER BY Id) AS NoId from data  where value = "yes"; 

db-fiddle.com demo