* JP <[EMAIL PROTECTED]> [2006-05-02 22:10]:
> SQLite provides a way to get the N-th row given a SQL statement, with
> LIMIT 1 and OFFSET <N>.
>
> Can the reverse be done in an efficient way? For example, given a table
> with 1million names, how can I return the row number for a particular
> element? i.e. something like
>
> SELECT rownum FROM
> (SELECT name,<rownum> FROM clients
> WHERE name='foo' ORDER BY name)
>
Assuming your client names are unique, this should work:
SELECT
(
SELECT
COUNT(*)
FROM clients c2
WHERE c2.name < c1.name
ORDER BY c2.name
) rank,
c1.name
FROM clients c1
ORDER BY rank;
On MySQL5 and PostgreSQL, it works as intended. Unfortunately,
SQLite complains that it doesn’t know about `c1.name`. I tried to
do it with a join to see if that would work better, but I’m too
frazzled to figure it out right now.
Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>
