How to get the last N records from a table

[Ricardo]

Hi all,

I am trying to come up with a sql query (just one query) that returns
all the columns for the last N records that were inserted into a
table. I have a column in the table that has the creation timestamp
for each record. In other words, if a client requests to get only the
latest 5 records, then this query shoud exactly do that: get all the
columns for the last 5 records that were inserted into the table.

[Mark A]

Re: How to get the last N records from a table

"Ricardo" <r_olivieri@yah oo.com> wrote in message
news:abeafe5d.0 310211910.26bac 37f@posting.goo gle.com...[color=blue]
> Hi all,
>
> I am trying to come up with a sql query (just one query) that returns
> all the columns for the last N records that were inserted into a
> table. I have a column in the table that has the creation timestamp
> for each record. In other words, if a client requests to get only the
> latest 5 records, then this query shoud exactly do that: get all the
> columns for the last 5 records that were inserted into the table.[/color]

ORDER BY the creation timestamp column in DESCENDING sequence. Then use the
FIRST n ROWS ONLY clause.

[Bob [IBM]]

Re: How to get the last N records from a table

Don't forget to have an index if possible to attempt to reduce table access.

--

Bob
Consulting I/T Specialist
IBM Toronto Lab
IBM Software Services for Data Management
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
"Mark A" <ma@switchboard .net> wrote in message news:jLmlb.1564 $Yp5.45065@news .uswest.net...
"Ricardo" <r_olivieri@yah oo.com> wrote in message
news:abeafe5d.0 310211910.26bac 37f@posting.goo gle.com...[color=blue]
> Hi all,
>
> I am trying to come up with a sql query (just one query) that returns
> all the columns for the last N records that were inserted into a
> table. I have a column in the table that has the creation timestamp
> for each record. In other words, if a client requests to get only the
> latest 5 records, then this query shoud exactly do that: get all the
> columns for the last 5 records that were inserted into the table.[/color]

ORDER BY the creation timestamp column in DESCENDING sequence. Then use the
FIRST n ROWS ONLY clause.

[Mark A]

Re: How to get the last N records from a table

> > Hi all,[color=blue][color=green]
> >
> > I am trying to come up with a sql query (just one query) that returns
> > all the columns for the last N records that were inserted into a
> > table. I have a column in the table that has the creation timestamp
> > for each record. In other words, if a client requests to get only the
> > latest 5 records, then this query shoud exactly do that: get all the
> > columns for the last 5 records that were inserted into the table.[/color]
>
> ORDER BY the creation timestamp column in DESCENDING sequence. Then use[/color]
the[color=blue]
> FIRST n ROWS ONLY clause.
>[/color]
You should probably create an index on the creation timestamp column and use
the ALLOW REVERSE SCANS clause on the CREATE INDEX.