On Thu, Jul 8, 2010 at 9:09 PM, Kenichiro Tanaka <ketanaka@xxxxxxxxxxxxxx> wrote: > Hello. > > I agree Ben. > But,I try your question as an SQL puzzle. > Doses this SQL meet what you want? > > select * from wantlast offset (select count(*)-10 from wantlast); that works, but for any non trivial query it's not optimal because it runs the complete query twice. if you are doing a lot of joins, etc. (or your query involves volatile operations) you might want to avoid this. cursors can do it: begin; declare c scroll cursor for select generate_series(1,1000); fetch last from c; -- discard result fetch backward 10 from c; -- discard result fetch 10 from c; -- your results commit; in 8.4 you can rig it with CTE: with foo as (select generate_series(1,1000) v) select * from foo offset (select count(*) - 10 from foo); the advantage here is you are double scanning the query results, not rerunning the query (this is not guaranteed to be a win, but it often will be). you can often rig it with arrays (dealing with non scalar type arrays is only possible in 8.3+) select unnest(a[array_upper(a, 1)-10:array_upper(a,1)]) from (select array(select generate_series(1,1000) v) as a) q; merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
