Number SQL rows based on query sorting?

Question

Say I have a set of SQLite rows that have an auto increment field "_id", a string field "title", an integer field "type", and an integer field "number".

There are only a few types (i.e. 1, 2, 3).
All the number fields are currently blank, and I need to fill them with the numbers 1 through N for all rows of type 1. These numbers should be in the same sorting order as the title field (Ascending).

After the rows have been numbered, SELECT * FROM TABLE WHERE type=1 ORDER BY title should return the exact same result as SELECT * FROM TABLE WHERE type=1 ORDER BY number.

I could do this by querying SELECT * FROM TABLE WHERE type=1 ORDER BY title and looping through all the _id's to update the numbers row by row, but I'm looking for a more efficient approach.

Answer 1

You could set the id to the number of rows with a smaller title:

UPDATE  YourTable
SET     Id = (
             select  count(*)
             from    YourTable t2
             where   t2.title < YourTable.title
             ) + 1

This assumes that there are no two rows with the same title.