Re: getting the last N tuples of a query

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, and in 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 limit with ascending or descending sorts to get the first or last, respectively.
>