mardi 24 mars 2015

Sqlite - Index of record

To describe my problem I have created the following database:


Table:



CREATE TABLE Town
(
IdTown integer PRIMARY KEY ASC,
Name TEXT
);


Records:



IdTown Name
1870 Brighlingsea
8723 Chelmsford
2263 Clacton on Sea
1263 Colchester
3299 Gt. Yarmouth
6565 Harwich
9526 Ipswich
9961 Kelverdon
7263 Manningtree
3589 Norwich
3647 Witham
6632 Wivenhoe


What I'm trying to achieve is to write a query that returns the index position of the record that contains a specific primary key value whilst the records are sorted in name order.


For example, if I pass in an IdTown of 9961 it will return 8 because Kelverdon is the 8th record when sorted in Name order.


In SQL Server (which I am more familiar with) I would use a query like this:



select Position
from
(
select IdTown, ROW_NUMBER() OVER(ORDER BY Name) AS position
from Town
) s1
where s1.IdTown = 9961


Does anyone know a way I can do this in SQLite?


Aucun commentaire:

Enregistrer un commentaire