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
