[sqlite] Documentation inconsistency

From here: https://www.sqlite.org/faq.html#q5 > (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round > up? This example seems to be no longer valid. Although the explanation is still valid in general, the particular example “SELECT ROUND(9.95,1)” actually returns 10.0

[sqlite] Issue (not bug) with generated columns

Consider this: .print Issue with generated columns create table a(n,s as (n+1)); insert into a values(1),(2),(3); select * from a; create table b as select * from a; .print table b converted the generated column into a regular column select * from b; delete from a; insert into a select * from

[sqlite] Issue (not bug) with generated columns

Consider this: .print Issue with generated columns create table a(n,s as (n+1)); insert into a values(1),(2),(3); select * from a; create table b as select * from a; .print table b converted the generated column into a regular column select * from b; delete from a; insert into a select * from

Re: [sqlite] Quick way to determine optimal page size?

, 2019 10:48 PM To: SQLite mailing list Subject: Re: [sqlite] Quick way to determine optimal page size? Not that I'm aware of no. How much of a difference are you seeing for your database size depending on the page size you try? -Original Message- From: sqlite-users On Behalf Of Tony

[sqlite] Quick way to determine optimal page size?

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and each database to determine which one produces the smallest file, is there some quicker way? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Double CTRL-C in shell get you completely out

Recently CTRL-C was improved to abort the query and stay in the CLI. This is very good. However, if (accidentally, e.g., key bounce) a second CTRL-C is entered, it will escape back to the console. Could it be changed so that either: 1. Only CTRL-D (Linux) or CTRL-Z (Windows) is used to exit

[sqlite] Unexpected/undocumented REPLACE() behavior

Example: --- CUT --- create table t(s text); insert into t values ('1'),('null'),('3'); .print 'BEFORE' select rowid,* from t; update t set s = replace(s,'null',null) --where s = 'null' --adding this works of course but that’s not my point ; .print 'AFTER' select rowid,* from t; --- CUT ---

[sqlite] Change delimiter with CSV extension?

Is it possible to change the delimiter with the CSV virtual table extension? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unclear about some shell command differences

Can someone explain the behavioral differences of the following shell commands? .backup ?DB? FILEBackup DB (default "main") to FILE .clone NEWDB Clone data into NEWDB from the existing database .save FILE Write in-memory database into FILE They all seem to

Re: [sqlite] readfile() enhancement request

...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Friday, May 17, 2019 12:34 PM To: SQLite mailing list Subject: [sqlite] readfile() enhancement request It’s quite often (for me, at least) the case I need to do something like this from the command line: >sqlite3.exe my.db “insert int

[sqlite] readfile() enhancement request

It’s quite often (for me, at least) the case I need to do something like this from the command line: >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text copied >from some other app’) The problem is the multi-line text cannot be copy-pasted directly into the command line

Re: [sqlite] Can't update repo

Oops! autonsync somehow was off. Thanks and apologies for noise. -Original Message- From: Richard Hipp Sent: Saturday, April 20, 2019 3:46 PM To: SQLite mailing list Subject: Re: [sqlite] Can't update repo On 4/20/19, Tony Papadimitriou wrote: My SQLite3 repo does not update

[sqlite] Can't update repo

My SQLite3 repo does not update to the latest. It stopped at 2019-04-17 [a3ab588329] c:\sqlite3>fossil up --- checkout: 03f2e78899fad99b0a0951b3a408268276954d4c 2019-04-17 21:12:05 UTC tags: trunk comment:

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

True, but SQLite3 is known to provide several conveniences that are not necessarily standard SQL. -Original Message- From: Simon Slavin Sent: Friday, April 12, 2019 7:11 PM On 12 Apr 2019, at 5:00pm, Tony Papadimitriou wrote: update t set s = replace(s, 'USA

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

I know this, thanks. I simply made a test case that can be run in MySQL, Postgreq and SQLite3. -Original Message- From: Chris Locke create table t(s varchar(5)); Also note that SQLite doesn't 'understand' varchar (it uses text) and it doesn't limit the entry to 5 characters. This

[sqlite] Unexpected or wrong result and no warning/error, bug?

create table t(s varchar(5)); insert into t values('US'),('USA'); update t set s = replace(s, 'USA', '___'), s = replace(s,'US','USA'), s = replace(s,'___','USA'); select * from t; -- Expected answer: -- USA -- USA -- --

[sqlite] Suggestions: '.mode insert table' output to be within transaction, and REPLACE equivalent

A couple of suggestions (I don’t know if they have come up before): 1. I often update the same table (e.g., a phone directory table) in various independent databases and it’s simpler to cascade the changes by doing this: sql a.db “.mode insert table” “select * from table” | sql b.db than with

Re: [sqlite] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

If it helps, I can reproduce with the mentioned binary on Win7 but I cannot with my own compiled version (using MSVC). -Original Message- From: Ralf Junker On 23.01.2018 15:31, Richard Hipp wrote: I'm still unable to reproduce this problem. sqlite3.exe from this ZIP:

Re: [sqlite] .DUMP displays floats differently from SELECT

I reported this same issue in May 2017 (http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html) I too consider this a problem (diffing dumps is one good reason why) but, unfortunately, this was an intentional change by this check-in: [7359fcac] Increase the

Re: [sqlite] Can an SQL script be built from within sqlite?

You can use .output file and .print for most cases (except those that need a calculated result) to simplify your script a bit. -Original Message- From: Shane Dev I have found a way achieve this purely in the SQLite shell. The trick is to make all rows in tcout1 SQL statements and

[sqlite] Compilation failure for sqlite3_analyzer

sqlite3_analyzer.c(207289): fatal error C1083: Cannot open include file: 'tcl.h': No such file or directory ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

MySQL does not seem to have a problem with it. -Original Message- From: Scott Robison On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou <to...@acm.org> wrote: create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group b

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

1)) SELECT group_concat(DISTINCT c,',') FROM t; Error: DISTINCT aggregates must have exactly one argument On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou <to...@acm.org> wrote: create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group by null;

[sqlite] GROUP_CONCAT with DISTINCT bug

create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group by null; -- OK select group_concat(distinct s) from t group by null; -- OK select group_concat(distinct s,', ') from t group by null; -- ERROR -- The moment the optional delimiter

[sqlite] PRAGMA help

Is there some way to get a list of all available pragma options from the SQLite3 shell? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 crash with NULL in snippet() function's 5th parameter

(SQLite v3.21.0 with FTS5 enabled) If instead of ‘’ (empty string) one uses NULL for the 5th parameter in the snippet() function it consistently crashes. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] printf() with UTF-8 and \n \t format

Great! Didn't think of the char() function at all. (Although I would prefer a platform independent \n) Thanks. -Original Message- From: Keith Medcalf So, is there any way to advance to next line from a command line printf()? sqlite> select printf('%s%s%s', 'line 1', char(10),

Re: [sqlite] printf() with UTF-8 and \n \t format

-Original Message- From: R Smith On 2017/12/19 8:37 PM, Tony Papadimitriou wrote: A couple of questions about printf 1. Does it work with UTF-8? If so, how? - Yes. - Very nicely. I'm using SQL v3.21 and UTF-8 does not work correctly. (Not from the command line.) I tried

[sqlite] printf() with UTF-8 and \n \t format

A couple of questions about printf 1. Does it work with UTF-8? If so, how? 2. Does it understand \n and \t? I put actual line breaks inside the string which is OK if run from script file but it won’t work with one-liners on the command-line. Thank you

Re: [sqlite] Is this a bug with expression evaluation?

-Original Message- From: J. King Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. I really don't know what the standard says, but here are two different opinions in implementation.

[sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a

[sqlite] Using computed column once in SELECT

I don’t know if this has come up before. Example: select cast((julianday('now')-julianday(dob))/365.25 as int) age, 87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy (... assuming 87 year average life span) This works, but as you see the age calculation has to be

Re: [sqlite] how into insert row into middle of table with integerprimary key

Possible something like this may work: create table fruit(id integer primary key, name text); insert into fruit values (1,'apple'), (2,'pear'), (3,'kiwi'); select * from fruit order by id; begin; update fruit set id = -id where id > 1; update fruit set id = 1-id where id < 0; end; insert

Re: [sqlite] Is this error expected?

Strike the last, my bad. It works correctly. I forgot to define the id part. So, just inconsistent. select * from (values(1,12345)) join (values(1,54321)) using(column1); -Original Message- From: Tony Papadimitriou Sent: Sunday, November 12, 2017 10:37 PM To: SQLite mailing list

Re: [sqlite] Is this error expected?

is the correct approach). -Original Message- From: Tony Papadimitriou It's actually even simpler to show this (without CTE defining a rowid): create table t1(v); insert into t1 values(12345); create table t2(v); insert into t2 values(54321); select * from t1 join t2 using(rowid

Re: [sqlite] Is this error expected?

lot about anticipated traffic volume. -Original Message- From: sqlite-users [mailto:sqlite-users- boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Sunday, 12 November, 2017 11:21 To: General Discussion of SQLite Database Subject: [sqlite] Is this error expected? An exa

[sqlite] Is this error expected?

An example to reproduce a problem I noticed: create table tab(val); insert into tab values(1); with t(rowid,val) as ( select rowid,val from tab union select rowid,tab.val from tab join t on t.rowid = tab.rowid --from tab join t

Re: [sqlite] Version 3.19.3 containing an important bug fix

Does this also affect FOSSIL, or is auto vacuum never used there? -Original Message- From: D. Richard Hipp Sent: Thursday, June 08, 2017 6:57 PM To: sqlite-annou...@mailinglists.sqlite.org ; sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Version 3.19.3 containing an important

Re: [sqlite] .DUMP displays floats differently from SELECT

Apparently, this was an intentional change by this check-in : [7359fcac] Increase the number of significant digits in floating point literals on ".dump" output from the shell. I don't know what problem this change actually solved (as in my case it introduced one) but for my own copy I changed

[sqlite] .DUMP displays floats differently from SELECT

While trying to search/replace some text from an SQLite3 dump I noticed that, unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT on the same values. I know all about expected floating point inaccuracies, but I don’t see why it should matter in this case as we have

Re: [sqlite] can not get data

LIKE is case insensitive, while = is not. -Original Message- From: lizhu...@whaty.com when I query : select * from downloadVideo_table where sectionId = '402814a34b823b23014bfc228fe9588c' then I query the length of 'sectionId', it is 32.that is right. select length(sectionId) from

Re: [sqlite] Which pragmas are persistent?

-Original Message- From: no...@null.net What would be useful (at least via the shell CLI) is a "list_pragmas" pragma that shows for example something like this: sqlite> PRAGMA list_pragmas; PRAGMA list; would be less redundant. ___

Re: [sqlite] Another .DUMP issue with v 3.18.0

-Original Message- From: Simon Slavin I notice that the command on those lines is "sql" rather than the "sqlite3" I would expect. Tony, do you get the same error if you type those things into the SQLite3 shell, rather than feeding them in using >your command shell ? It works

Re: [sqlite] Another .DUMP issue with v 3.18.0

alf Of Tony Papadimitriou Sent: Friday, 7 April, 2017 17:04 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0 -Original Message- From: Josh Hunsaker >On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote: >&g

Re: [sqlite] Another .DUMP issue with v 3.18.0

-Original Message- From: Josh Hunsaker On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote: On 4/7/17, Tony Papadimitriou wrote: sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')" sql .dump xxx.db | sql I'm unable to repro. Is t

[sqlite] Another .DUMP issue with v 3.18.0

The following dump cannot be used to rebuild the database because the column name is not properly quoted giving an error. To reproduce: sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')" sql .dump xxx.db | sql Error: near line 4: near "zone": syntax error

Re: [sqlite] How to circumvent UNIQUE constraint

Here's one possibility (simplified table for example): create table desktops( indexno integer not null unique, nametextnot null primary key ); insert into desktops values (1,'CompA'), (2,'CompB'), -- we want to insert new record here bumping all above by one (3,'CompD'), (4,'CompE');

Re: [sqlite] Autoincrement sequence not updated by UPDATE

Well, I got different results (so maybe it's version related). I tried it on MySQL v5.7.16-log: ++--+ | id | v| ++--+ | 2 | two | | 10 | one | ++--+ ++--+ | id | v| ++--+ | 10 | one | | 40 | two | ++--+ ++---+ | id | v |

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

>> > 1. Why SQLite is popular. >> >> The answers to those question mentioned in the podcast may be good ones >> but I >> think the main reason is that it's free. Completely, unmistakably, free. > >Necessary but not sufficient. >It's free, and the license is as non-restrictive as it is possible

[sqlite] .DUMP output compatibility

Windows! -Original Message- From: Richard Hipp Sent: Friday, May 06, 2016 1:55 AM To: SQLite mailing list Subject: Re: [sqlite] .DUMP output compatibility On 5/5/16, Tony Papadimitriou wrote: > Is it possible for .DUMP to produce table/field names quoted with ` > (backquote) i

[sqlite] .DUMP output compatibility

Is it possible for .DUMP to produce table/field names quoted with ` (backquote) instead of ? (double quote) for compatibility with MySQL? Or is this already adjustable by some setting I missed? Thank you.

[sqlite] CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP

BTW, the following with xxx(current_date,date) as ( select '2000-01-01',current_date ) select *,current_date,date from xxx; outputs: current_date datecurrent_date date -- -- 2000-01-012016-02-10 2016-02-102016-02-10 As

[sqlite] SQLDIFF problem

You're probably right. You can try this shorter schema and get the same problem. create table xxx("" text); However, my point was that if SQLite3 allows it, SQLDIFF should be able to handle it also. Thanks. -Original Message- From: Tim Streater Sent: Monday, November 30, 2015 12:41

[sqlite] Small bug in ".dump", ".schema" and ".fullschema"

I tried your example. '.schema' and '.fullschema' do show the problem you describe. However, for me, '.dump' worked correctly placing the semicolon on a line by itself right after the comment. - Original Message - From: "sqlite-mail" To: Sent: Wednesday,

Re: [sqlite] Whish List for 2015

lson, Erik - 2 Sent: Tuesday, December 23, 2014 5:35 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Whish List for 2015 Tony Papadimitriou Sent on Tuesday, December 23, 2014 10:26 AM Problem 1: Currently, any scripts have to be stored outside the database in separate files mea

Re: [sqlite] Whish List for 2015

OK, if everyone if putting down their wish list for 2015, here's mine: Using the SQLite shell is very convenient for quick retrieval or modification of data any SQLite database but there are two significant problems (in my view) that make this of very limited utility, and this proposed

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted bylength

I forgot to also factor out the ORDER BY. So, the updated query is: - with t as ( select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' order by Length desc )

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

One obvious shortcut would be to factor out the common part of the select using WITH (Hopefully accurate) example: with t as ( select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' ) select *

Re: [sqlite] Is there equivalent to MySQL IF() function?

Totally agree. And this is exactly why my natural first reaction was to expect a syntax error, because in my mind you can't compare a non-boolean expr to a boolean expr. I need to start C-ing everything differently. Not easy... :) Thank you (and I didn't imply there was malice, ... just a

Re: [sqlite] Is there equivalent to MySQL IF() function?

Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a bit misleading documentation, that I got confused. It is usually those who are used to only the C-like treatment of a boolean result as being equivalent to an

Re: [sqlite] Is there equivalent to MySQL IF() function?

OK, I think I managed to figure out what it means by reading a little more about the two CASE cases. 'CASE expr WHEN' compares the base expr with the WHEN expr, whereas 'CASE WHEN' compares the WHEN expr to true So, the difference is more than just how many times 'x' is evaluated. (Maybe the

Re: [sqlite] Is there equivalent to MySQL IF() function?

Database Subject: Re: [sqlite] Is there equivalent to MySQL IF() function? On Tue, Oct 7, 2014 at 1:13 PM, Tony Papadimitriou <to...@acm.org> wrote: > As you can see, the second select gives unexpected output, and according > to the syntax diagram it's not supposed to be a val

Re: [sqlite] Is there equivalent to MySQL IF() function?

014/10/07 11:15, Tony Papadimitriou wrote: Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADUL

[sqlite] Is there equivalent to MySQL IF() function?

Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT")); If not, please add to wish list :) TIA ___ sqlite-users mailing list

Re: [sqlite] archive of database

My recommendation is to dump the SQLite database into a corresponding .sql file, and then compress that ASCII file using your favorite compressor (ZIP, RAR, etc.) In my view, this method has two advantages over copying the actual binary file for backup purposes, but no obvious disadvantages.

Re: [sqlite] SQLite version 3.8.6 coming soon

Unless I'm misusing it, I believe the shell readfile() function reads a file as text, not binary. I haven't checked writefile() for the same problem on writing. This is how I use it, is this not correct? create table t1(a blob); insert into t1 values(readfile('binfile')); select * from t1;

Re: [sqlite] Send parameter from batch file to sqlite

Not possible directly from SQLite. Some weeks ago I suggested a possible way to have this capability added to the shell version of SQLite3 but there seems to be zero interest from the developers. So, don't hold your breath. Better yet, write a Lua script or something to do it. (If you do,

Re: [sqlite] Is it possible to see timeline for given branch (or eventag)?

Oops! That was meant for the fossil group, sorry! -Original Message- From: Tony Papadimitriou Sent: Thursday, March 27, 2014 7:26 PM To: General Discussion of SQLite Database Subject: [sqlite] Is it possible to see timeline for given branch (or eventag)? When I give the command

[sqlite] Is it possible to see timeline for given branch (or even tag)?

When I give the command FOSSIL TIM it lists all changes in all branches (and tags). But what if I want to see changes in only, say, the TRUNK branch? Is there a way? Or maybe items having a given tag? TIA ___ sqlite-users mailing list

Re: [sqlite] SQLite clusters?

A "temp" view, however, can access table from different DBs. -Original Message- From: Gerry Snyder I was under the impression that a view is limited to the tables in the db file where it resides, and have received error messages whenever I tried to access another file's tables.

Re: [sqlite] quetion on editing a sqlite database

See the REPLACE(x,y,z) function. Use UPDATE table SET field = REPLACE(field,) -Original Message- From: john white Sent: Monday, September 16, 2013 1:30 AM To: sqlite-users@sqlite.org Subject: [sqlite] quetion on editing a sqlite database I have a bit of a problem. I am moving a

Re: [sqlite] Hints for the query planner

How about: maybe(COLUMN LIKE '%pattern%',.95) or (as percent using integer value in 0..100) maybe(COLUMN LIKE '%pattern%',95) with a default value of (possibly) 50% (or .5) for the optional second arg? -Original Message- From: Richard Hipp Sent: Tuesday, September 10, 2013 10:26 PM

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

This is documented, I think. Based on 2.1 (bullet 5) of http://www.sqlite.org/datatype3.html the default affinity is numeric. Since STRING is not understood, it has numeric affinity. -Original Message- The actual table definition is : CREATE TABLE T_VWG_ABFALLVERZEICHNIS

Re: [sqlite] Bug in query planner / optimizer

With the SQL query at the end of the dump like you say, try this: SQLite3 -init dump.sql Then, no matter how many times you repeat the query from within SQLite3 prompt, it works! As soon as you do ANALYZE, the query stops working... Maybe this helps pin point the problem. (Replacing "

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

OK, you don't agree. Your opinion! (That doesn't make you right, though!) I'm sure there will be a SQL engine somewhere that will do it for you. We're talking about SQLite here, aren't we? If some other database can do it, then you should also consider that it may also be able to do what

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

I agree with you completely! It has to be relatively simple to implement. And certainly it adds a very important missing functionality (for those who can see it, that is). Some kind of internal cursor has to iterate to return rows to the user after the query is completely executed. This can

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

Thanks! (At least some understand what I mean!) And people giving examples of how it can be done in C (or Python, for me) or whatever language miss the point. We're not talking how it can be programmatically. This is easy!!! How does one do it via pure SQL is the real question. As for

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

Please! Just because you can select something doesn't mean you have to be able to sort by it. Can you sort by * (select * by table sort by *)? So, why make it sound like I don't know what I'm talking about? -Original Message- From: Alex Bowden Sent: Monday, July 01, 2013 2:07 PM

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

Subject: Re: [sqlite] Is there a way to return the row number? (NOT therowid) I can't wait to try order by row_number desc On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote: Is there a function (or method), e.g., row(), to return the sequence number of the select

[sqlite] Is there a way to return the row number? (NOT the rowid)

Is there a function (or method), e.g., row(), to return the sequence number of the selected row? This is not the same as ROWID. row() should give a sequence number always starting from 1 up the to the number of rows returned by the view/select etc. If not, then please add to the wish list.