[sqlite] Getting row number in a sorted list.

[R.Smith]

>
> SELECT count(sub.Name) + 1 AS Rank,  a.Name
>   FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name < 
> a.Name
>  WHERE a.Name LIKE 'P%'
>  GROUP BY a.Name
>  ORDER BY a.Name
>  LIMIT 1
>
>
>   --     Rank     | Name
>   -- ------------ | ------
>   --       4      | PQRS

I should add that the group by is just there to make the COUNT() behave 
as expected, if you need the rank even in duplicates when there are 
multiples of the same name (not that that would make any sense 
realistically, but let's imagine there exists such a use-case) then 
simply add a temp table with a column with RANDOM() appended to the name 
and use that for the order.

Same example as before but with duplicate names:

create table NameTable(Name TEXT);

insert into NameTable VALUES
('PTN'),
('ABCD'),
('CDE'),
('ABCD'),
('PQRS'),
('ABCD'),
('ABCD'),
('AXN');

CREATE TEMPORARY TABLE tmpTable AS SELECT Name, Name||random() AS UName 
FROM NameTable;

SELECT * FROM tmpTable;

   -- Name   | UName
   -- ------ | --------------------------
   -- PTN    | PTN670595216556973252
   -- ABCD   | ABCD3088193799719600707
   -- CDE    | CDE2011182050635024217
   -- ABCD   | ABCD-6134681665725239567
   -- PQRS   | PQRS1314027443609404785
   -- ABCD   | ABCD4099207489085812545
   -- ABCD   | ABCD-7190663061184182030
   -- AXN    | AXN9089277539697356029


SELECT count(sub.UName) + 1 AS Rank,  a.Name
   FROM tmpTable AS a LEFT OUTER JOIN tmpTable AS sub ON sub.UName < a.UName
  GROUP BY a.UName
  ORDER BY a.UName;

   --     Rank     | Name
   -- ------------ | ------
   --       1      | ABCD
   --       2      | ABCD
   --       3      | ABCD
   --       4      | ABCD
   --       5      | AXN
   --       6      | CDE
   --       7      | PQRS
   --       8      | PTN


SELECT count(sub.UName) + 1 AS Rank,  a.Name
   FROM tmpTable AS a LEFT OUTER JOIN tmpTable AS sub ON sub.UName < a.UName
  WHERE a.Name LIKE 'P%'
  GROUP BY a.UName
  ORDER BY a.UName
  LIMIT 1;

   --     Rank     | Name
   -- ------------ | ------
   --       7      | PQRS



Also: Add temp Index on the temp table if it is large and clean up the 
table afterward, of course.

NOTE: A query asking for the rank of 'ABCD' or even LIKE 'A%' will 
ALWAYS return 1 because the first 'ABCD' is at position 1, even though 
there are 'ABCD' names ranked at 2, 3 and 4. (Which is why this is 
non-sensical, but there it is no less!).