0101 0101 - 10 months ago 57
SQL Question

How to select records with order id in SQLite

I have a query that selects data in an alphabetical order. For example:

6 |A
35 |AA
3 |B
87 |BB

And I would like to select an
order id
of the record as well (
order id
it's not an actual column in the db). So the result should look like:

6 |0 |A
35 |1 |AA
3 |2 |B
87 |3 |BB

How can I achieve something like this? I could check every returned record in a while loop and I would increase a counter on every iteration but that's not really what I want to do and I would rather leave this on database.


This is kind RANK function in other database engines.

In SQLite, you can achieve this like:

SELECT ID, (SELECT COUNT() FROM table AS t WHERE Title<table.Title), Title FROM table;

dense_rank and sqlite - possible?

EDIT: Distinct order numbers:

SELECT ID, (SELECT COUNT() FROM table AS t WHERE Title<table.Title OR Title=table.Title AND ROWID<table.ROWID), Title FROM table;