Holy cow that feels inefficient. It's a bit clunky, but why not insert into a temporary table, ordered as desired, and then use the rowid from the temp table?
On Fri, Mar 27, 2009 at 3:48 PM, Kees Nuyt <[email protected]> wrote: > On Fri, 27 Mar 2009 12:52:23 -0500, Robert Citek > <[email protected]> wrote: > >>How can I get a query to display sequential row number in a select statement? >> >>I have a simple database similar to this: >> >>$ sqlite3 db .dump >>BEGIN TRANSACTION; >>CREATE TABLE foo (field); >>INSERT INTO "foo" VALUES('a'); >>INSERT INTO "foo" VALUES('b'); >>INSERT INTO "foo" VALUES('c'); >>COMMIT; >> >>$ sqlite3 db 'select * from foo ; ' >>a >>b >>c >> >>I would like to do the equivalent of this in SQL: >> >>$ sqlite3 db 'select * from foo order by field desc ; ' | cat -n >> 1 c >> 2 b >> 3 a >> >>I have looked into rowid but it keeps the actual row id from the table >>and does not reorder the rowid based on the sort order. >> >>Pointers to references appreciated. > > sqlite_version():3.6.11 > CREATE TABLE test1(b TEXT); > INSERT INTO test1(b) VALUES('hello A'); > INSERT INTO test1(b) VALUES('hello B'); > INSERT INTO test1(b) VALUES('hello C'); > SELECT * FROM test1; > > b > hello A > hello B > hello C > > select (select COUNT(0) > from test1 t1 > where t1.b <= t2.b > ) as 'Row Number', b from test1 t2 ORDER BY b; > > Row Number|b > 1|hello A > 2|hello B > 3|hello C > > select (select COUNT(0) > from test1 t1 > where t1.b >= t2.b > ) as 'Row Number', b from test1 t2 ORDER by b DESC; > > Row Number|b > 1|hello C > 2|hello B > 3|hello A > >>Regards, >>- Robert > -- > ( Kees Nuyt > ) > c[_] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
