CODEWITHSUNDEEP
sqliteandroid-sqliterow-number
Puja Surya
Puja Surya

Reputation: 1287

How to use ROW_NUMBER in sqlite

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.

Upvotes: 46

Views: 115002

Answers (6)

Nelson
Nelson

Reputation: 2177

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

Upvotes: 4

sadegh salehi
sadegh salehi

Reputation: 37

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

Upvotes: -2

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

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

Upvotes: 41

v12golfcart
v12golfcart

Reputation: 33

UPDATE: sqlite3 version 3.25 now supports window functions including:

row_number() over(order by id)

SQLITE3 Documentation

Upvotes: 2

user3198319
user3198319

Reputation: 21

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

Upvotes: 2

Meherzad
Meherzad

Reputation: 8553

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 |

Upvotes: 32

Related Questions