35

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.

vishal_g
  • 3,829
  • 4
  • 20
  • 33
Puja Surya
  • 1,055
  • 4
  • 20
  • 44
  • 3
    http://stackoverflow.com/questions/9151706/getting-row-number-for-query – Scotch May 30 '13 at 23:39
  • 1
    Is `id` a autoincrement or a PK column? Have your tried `SELECT _ROWID_`? – Alix Axel May 30 '13 at 23:41
  • 1
    @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 '13 at 23:44
  • @AlixAxel So query the whole table? – Scotch May 30 '13 at 23:52
  • @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 '13 at 23:54
  • That's true. I guess people should use something like that until somethhing like rownum or row number() is implemented. – Scotch May 30 '13 at 23:56
  • @all edited my question. – Puja Surya May 31 '13 at 00:02
  • Possible duplicate of [how to get rowNum like column in sqlite IPHONE](https://stackoverflow.com/questions/14023292/how-to-get-rownum-like-column-in-sqlite-iphone) – Diego Rafael Souza Jul 02 '18 at 16:14

4 Answers4

29

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

Adobe
  • 11,926
  • 7
  • 81
  • 121
Lukasz Szozda
  • 139,860
  • 19
  • 198
  • 228
  • 1
    Wonderful. It works very well on aggregate functions too. – rayzinnz Sep 26 '18 at 06:21
  • 1
    +1 I want to thank you for providing this critical information. I've been beating my skull against a wall for the past day trying to figure out why I was getting a syntax error when trying to use the row_number function using a java JDBC driver. Couldn't find any info on it until I saw your comment and realized I was running version 3.20.1. After upgrading versions I can now use the window functions!! Thanks again. – Jake Henry Feb 08 '21 at 21:33
  • 1
    Best solution since ```ROW_NUMBER()``` was added to sqlite3. – Joan Lara Ganau Jul 04 '21 at 22:41
27

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 |
Meherzad
  • 8,148
  • 1
  • 28
  • 40
2

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
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
user3198319
  • 21
  • 1
2

UPDATE: sqlite3 version 3.25 now supports window functions including:

row_number() over(order by id)

SQLITE3 Documentation

v12golfcart
  • 33
  • 4