Thread: getting the last N tuples of a query
Hi! if a want the first 5,10,N tuples of a query (even without order) i just have to do a: select * from table limit 10; but, What can i do to get the last 10 tuples ??? i try to do: select * from table limit -10; :-D but that query return 0 tuples . So, what is the right way to do that with no order????
On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: > Hi! > > if a want the first 5,10,N tuples of a query (even without order) > i just have to do a: > select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whichever 10 it can, andin practice, the results will change given enough inserts or deletes. > So, what is the right way to do that with no order???? Without an order by clause, there is no concept of "first" or "last". Once you have the order by clause, combine your limitwith ascending or descending sorts to get the first or last, respectively.
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); --test case create table wantlast(col1 int); insert into wantlast select g from generate_series(1,1000) as g; postgres=# select * from wantlast offset (select count(*)-10 from wantlast); col1 ------ 991 992 993 994 995 996 997 998 999 1000 (10 rows) postgres=# analyze wantlast ; ANALYZE postgres=# explain select * from wantlast offset (select count(*)-10 from wantlast); QUERY PLAN -------------------------------------------------------------------------- Limit (cost=17.91..30.52 rows=900 width=4) InitPlan 1 (returns $0) -> Aggregate (cost=16.50..16.52 rows=1 width=0) -> Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=0) -> Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=4) (5 rows) *I try this test Postgresql8.4.4 > On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: > > >> Hi! >> >> if a want the first 5,10,N tuples of a query (even without order) >> i just have to do a: >> select * from table limit 10; >> > That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whichever 10 it can, andin practice, the results will change given enough inserts or deletes. > > >> So, what is the right way to do that with no order???? >> > > Without an order by clause, there is no concept of "first" or "last". Once you have the order by clause, combine your limitwith ascending or descending sorts to get the first or last, respectively. >
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