I have an SQLite table, and I need to add a column that contains the row position as given by a SELECT instruction.

The SELECT instruction contains two order clauses.

In case it's not clear, here is an example:

Given this data:

id        | c1      | c2 
===============================
a         | 1       | 2     
b         | 1       | 3     
c         | 1       | 1     
d         | 2       | 2     
e         | 2       | 3     
f         | 2       | 1     

And this query:

SELECT * FROM ta开发者_JAVA技巧ble ORDER BY c1, c2

I need this resulting table:

id        | c1      | c2      | position
=========================================
a         | 1       | 2       | 2
b         | 1       | 3       | 3
c         | 1       | 1       | 1
d         | 2       | 2       | 5
e         | 2       | 3       | 6
f         | 2       | 1       | 4

How can I do it?

IMPORTANT NOTE: This is not the actual data or even the actual columns. it's just an example.

This query should give you all of the fields that you are after:

SELECT 
   ( SELECT COUNT(0)
     FROM MyTable T1
     WHERE (T1.c1 * 1000) + T1.c2 <= (T2.c1 * 1000) + T2.c2
   ) as 'position', 
   c1,
   c2,
   ID
FROM 
   MyTable T2
ORDER BY 
   (c1 * 1000) + c2

Now, to order them by ID:

SELECT ID, c1, c2, position FROM
  (SELECT 
     (SELECT COUNT(0)
       FROM MyTable T1
       WHERE (T1.c1 * 1000) + T1.c2 <= (T2.c1 * 1000) + T2.c2
     ) as 'position', 
     c1,
     c2,
     ID
  FROM
     MyTable T2 
  ORDER BY 
     (c1 * 1000) + c2
  ) T3
ORDER BY ID

Notes

• If the inner Order By does not work, you can dump the intermediate results into a temp table, then select from the temp table with the final Order By clause.
• The "1000" above is a coefficient. You need to make sure that this coefficient is greater than the maximum value of c2, but not so high that you get an integer overflow.

Alternative Method

You can insert into a temporary table with an AutoIncrement column, and then select the values from that temp table based upon your desired ordering.

http://www.sqlite.org/autoinc.html