Re: getting the last N tuples of a query

On Thu, Jul 8, 2010 at 9:09 PM, Kenichiro Tanaka
<ketanaka@ashisuto.co.jp> 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