AFAIK there is no such internal counter. As is easily verifiable via EXPLAIN, the LIMIT clause is implemented via SQLite registers in pseudocode.
.explain create temp table test (a,b,c); explain select * from test limit 3; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Integer 3 1 0 00 LIMIT counter <-- initialized here 2 Goto 0 13 0 00 NULL 3 OpenRead 0 2 1 3 00 test 4 Rewind 0 11 0 00 NULL 5 Column 0 0 2 00 test.a 6 Column 0 1 3 00 test.b 7 Column 0 2 4 00 test.c 8 ResultRow 2 3 0 00 NULL 9 IfZero 1 11 -1 00 NULL <-- tallied here 10 Next 0 5 0 01 NULL 11 Close 0 0 0 00 NULL 12 Halt 0 0 0 00 NULL 13 Transaction 1 0 0 00 NULL 14 VerifyCookie 1 1 0 00 NULL 15 TableLock 1 2 0 test 00 NULL 16 Goto 0 3 0 00 NULL asql> explain select * from test; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 11 0 00 NULL 2 OpenRead 0 2 1 3 00 test 3 Rewind 0 9 0 00 NULL 4 Column 0 0 1 00 test.a 5 Column 0 1 2 00 test.b 6 Column 0 2 3 00 test.c 7 ResultRow 1 3 0 00 NULL 8 Next 0 4 0 01 NULL 9 Close 0 0 0 00 NULL 10 Halt 0 0 0 00 NULL 11 Transaction 1 0 0 00 NULL 12 VerifyCookie 1 1 0 00 NULL 13 TableLock 1 2 0 test 00 NULL 14 Goto 0 2 0 00 NULL -----Ursprüngliche Nachricht----- Von: Gabriel Corneanu [mailto:gabrielcorne...@gmail.com] Gesendet: Montag, 01. Juli 2013 13:11 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Is there a way to return the row number? (NOT the rowid) I also needed this for some special update queries. Without many details, it was some kind of "insert into xxx select <row()>, otherfields from source order by <custom function>". For this case there is a workaround, selecting first into a temporary table with auto generated rowid and using it afterwards for insert. There are lots of other cases where this would be handy, e.g. showing ordinal of some results. Of course it is simple to implement when the loop is under your control. But sometimes there are libraries (components) which are interfaced only by a cursor. E.g a listing/grid showing cursor results, where you can NOT control the implementation but want to show "row" as a column. I currently use another workaround for this, declaring a user function which simply increments/returns a counter. As long as the queries are simple it is ok, but wrong usage is easily possible (e.g. multiple times per row). As the sqlite query program already has an internal loop, it would be very easy to increment a counter for each "next" opcode and return it via a special function or pseudo column. I believe such a counter already exists for "LIMIT" clause, so most of the work is already done... Regards, Gabriel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
