On 2015-08-19 03:02 PM, John McKown wrote:
> On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin <slavins at bigfraud.org> wrote:
>
>> 1|ABCD
>> 2|CDE
>> 4|AXN
>> 5|AXN2
>> 6|PQRS2
>> sqlite> select rowid from NameTable where Name between 'P' and 'PZZZZZZZ'
>> limit 1;
>> 6
>>
>>
>> Hum, that probably isn't what the OP wanted. I would guess in this latter
>> table, he would want "5" because that is the _relative_ row number you
>> would see by counting if you did a simple "SELECT Name FROM NameTable;"
>>
>> The basic problem is that the question is "improper". In general, there is
>> no "relative row number" for the rows listed by a "SELECT" command. Oh,
>> there is when you look at it. But the order of the rows returned where then
>> is no ORDER BY clause is not guaranteed. And, even then, the order of
>> individual rows which have the same ordering with the ORDER BY are not
>> guaranteed. What I mean is, if you do SELECT A,B,C FROM TABLE ORDER BY A, B
>> ; and there are two or more rows with equal A & B values, then the order of
>> the C values is not guaranteed.
>>
>> So, what to do? Well, it would be possible to do something like:
>>
>> sqlite> drop table if exists row_order;
>> sqlite> create temporary table row_order AS select * from NameTable;
>> sqlite> select rowid from row_order where Name between 'P' and 'PZZZZZZZ'
>> limit 1;
>> 5
>> sqlite> select rowid, Name from row_order;
>> 1|ABCD
>> 2|CDE
>> 3|AXN
>> 4|AXN2
>> 5|PQRS2
>> sqlite> select rowid, Name from NameTable;
>> 1|ABCD
>> 2|CDE
>> 4|AXN
>> 5|AXN2
>> 6|PQRS2
>> sqlite>
>>
>> This may answer the OP's question. But it only works for SQLite. And I am
>> not sure that it is guaranteed to work on all past and future versions of
>> SQLite. It depends on the non-standard ROWID facility in SQLite. Perhaps
>> Dr. Hipp can address this last issue.
I agree the OP's question is not well-formed. We do not know if he needs
the row id in order to effect an update perhaps, or if he needs the rank
in terms how-many-eth a row appears in an ordered list.
If it is simply the row id, then Simon's solution will work perfectly,
and if it is the rank he would rather want, your solution would work on
SQLite - but there is also a more universal SQL based approach that
would work on any DB by simply linking a table to itself and counting
the instances lower down the order, like this quick script demonstrates:
(Ref: I was first shown this method by James Lowden as explained on
schemamania.org)
create table NameTable(Name TEXT);
insert into NameTable VALUES
('PTN'),
('ABCD'),
('PZZZ'),
('CDE'),
('PQRS'),
('AXN');
SELECT rowid, Name FROM NameTable;
-- rowid | Name
-- ------------ | ------
-- 1 | PTN
-- 2 | ABCD
-- 3 | PZZZ
-- 4 | CDE
-- 5 | PQRS
-- 6 | AXN
-- Rank the names in Alphabetical order:
--
SELECT count(sub.Name) + 1 AS Rank, a.Name
FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name < a.Name
GROUP BY a.Name
ORDER BY a.Name;
-- Rank | Name
-- ------------ | ------
-- 1 | ABCD
-- 2 | AXN
-- 3 | CDE
-- 4 | PQRS
-- 5 | PTN
-- 6 | PZZZ
-- And now to only get the specific item's rank, simply include
-- a WHERE clause and LIMIT clause:
--
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
-- 2015-08-19 15:32:33.424 | [Success] Script Success.
