On 2014/09/18 05:38, Mark Halegua wrote:
I'm racking my brain trying to figure out how to get directly to the last item
in a (potentially)
sorted or ordered table. At least oe of the tables will be ordered by a name
and a date,
so uising the rtowid won't work.
Also, how to traverse a table or cursor in a reverse direction.
Hi Mark, Keith's reply is quite complete and you should design systems with
those tenets in mind.
That said - to elaborate more on the quick and dirty methods (in case you are
hacking together a quicky in stead of a proper system):
Getting only the last item in table t which has fields fDate and fName among
others:
SELECT * FROM t WHERE 1 ORDER BY fDate DESC, fName DESC LIMIT 1;
(The DESC does the trick of making the sort order go the other way round, the LIMIT is to get just the 1st item [which is really the
last item had the sort-order been normal] and the WHERE clause is superfluous here, but shows where it might be. Also note that this
method will be real fast if fDate and fName are contained in Indices, else it may well be quite slow for large tables)
Traversing the results in reverse is achieved much the same way, just remove the limit clause. If however you meant that you need to
move "back" through the list after having gone forward or such, then you are in scrolling cursor territory and I respectfully refer
you back to Keith's article and urge to take heed of the advice.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
