"Dennis Volodomanov" <i...@psunrise.com> wrote in message news:4a1e80dd.9040...@psunrise.com > Igor Tandetnik wrote: >>> and let's say, "Data" has an "ID" field (primary index). >>> >>> Is it possible to find out the *position* in the returned result set >>> of a Data with a given ID? >>> >> >> select count(*) from Data >> where PlotOnGraph=1 >> and Date < (select Date from Data where ID=?); >> >> > This works well, until if hits a non-unique date (which happens often > in this application). Is there a way to make it work with non-unique > dates as well?
Well, there's no way to tell how records with the same Date are going to end up ordered in the original query. If you impose some deterministic order there, e.g. with ORDER BY Date, Id then you can do something like this: select count(*) from Data d, (select Date, ID from Data where ID=?) myRow where PlotOnGraph=1 and d.Date <= myRow.Date and (d.Date < myRow.Date or d.ID < myRow.ID); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
