Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                @Scotch: It's not exactly duplicate, because the OP seems to be asking about the ROWID of a full table scan, rather than the ROWID of a specific query.
– Alix Axel
                May 30, 2013 at 23:44
                @Scotch: The data seems to suggest that, yes. I know it's also possible to overcome that with the answer you linked to, but it might be unnecessary and slower (depending on the column definition for id).
– Alix Axel
                May 30, 2013 at 23:54
  
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
I mended somewhat with fiddleanswer and got exactly the result as expected
select id, value , 
       (select count(*) from data b where a.id >= b.id and b.value='yes') as cnt 
from data a where  a.value='yes';
result
1|yes|1
3|yes|2
4|yes|3
6|yes|4
9|yes|5
                This only means, that there is no such functionality in SQLite. I wonder if this is O(n^2) by the way...
– Tomasz Gandor
                Nov 18, 2015 at 13:54
UPDATE: sqlite3 version 3.25 now supports window functions including:
row_number() over(order by id)
SQLITE3 Documentation
The ROW_NUMBER() windowing function can be done over an empty ORDER() like so (credit to @forpas):
select *, ROW_NUMBER() OVER() AS NoId
from data 
where value = "yes";
                ORDER BY (SELECT NULL) will return an arbitrary row number. Also, it is not at all needed because just OVER () will do the same.
– forpas
                Jan 13 at 8:17
                Thanks. Actually, performance wise, OVER () has no overhead while OVER (ORDER BY (SELECT NULL)) does, on top of it potentially returning arbitrary numbers.
– Nelson
                Jan 16 at 2:00
                Welcome to Stack Overflow. Code is a lot more helpful when it is accompanied by an explanation. Stack Overflow is about learning, not providing snippets to blindly copy and paste. Please edit your answer and explain how it answers the specific question being asked. See How to Answer.
– Chris
                Jun 27, 2022 at 0:25
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.