>
> 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!).
