0101 0101 - 6 months ago 31
SQL Question

How to select records with order id in SQLite

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

ID |TITLE
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:

ID |ORDER ID |TITLE
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.

Answer

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;
Comments