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.

Neglect

#9152206 – Pastoral

Is id a autoincrement or a PK column? Have your tried SELECT _ROWID_? – Bilbo

@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. – Bilbo

@AlixAxel So query the whole table? – Pastoral

@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). – Bilbo

That's true. I guess people should use something like that until somethhing like rownum or row number() is implemented. – Pastoral

Possible duplicate of how to get rowNum like column in sqlite IPHONE – Yippee

Answer 1

Try this query

select id, value, (select count(*) from tbl b  where a.id >= b.id) as cnt
from tbl a

FIDDLE

| id | value | cnt |
--------------------
|  1 |   yes |   1 |
|  3 |   yes |   2 |
|  4 |   yes |   3 |
|  6 |   yes |   4 |
|  9 |   yes |   5 |

Trainor

Answer 2

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

Auliffe

Answer 3

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";

Lindeberg

ORDER BY (SELECT NULL) will return an arbitrary row number. Also, it is not at all needed because just OVER () will do the same. – Intracellular

Thanks. Actually, performance wise, OVER () has no overhead while OVER (ORDER BY (SELECT NULL)) does, on top of it potentially returning arbitrary numbers. – Lindeberg

Answer 4

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

Timepleaser

This only means, that there is no such functionality in SQLite. I wonder if this is O(n^2) by the way... – Soiree

Yes, this is n^2-ish. Don't do it :) – Concession

Answer 5

UPDATE: sqlite3 version 3.25 now supports window functions including:

row_number() over(order by id)

SQLITE3 Documentation

Glorify

Answer 6

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

Sepulchre

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. – Filmdom