SELECT-how to get only the last 20 records?

[siatki]

Hello,
Firstly, sorry for my english.
I have problem with creating SQL statement. I am beginner and I think that
it is very easy to do. Look -
I have to get only the last 20 records from table ABC (f.eg.) and according
to that how to create the SQL statement? F.eg. SELECT name, forename, date
FROM abc WHERE....
You know - how to finish the statement to get only 20 records lastly
inserted into the table?
Thanks and I am waiting for helpfull answers.

[twbanks]

I'll take a stab at the easy answer.

If there is a timestamp or identity field on this table, you could do:

select top 20 name, etc.
from abc
order by x desc

where x would be the name of the timestamp or identity field.

Without one of those fields, it gets more difficult and I'll leave that
to other experts.

[Hugo Kornelis]

On Mon, 21 Feb 2005 22:03:23 +0100, siatki wrote:

Hello,
Firstly, sorry for my english.
I have problem with creating SQL statement. I am beginner and I think that
it is very easy to do. Look -
I have to get only the last 20 records from table ABC (f.eg.) and according
to that how to create the SQL statement? F.eg. SELECT name, forename, date
FROM abc WHERE....
You know - how to finish the statement to get only 20 records lastly
inserted into the table?
Thanks and I am waiting for helpfull answers.

Hi siatki,

SQL Server won't automatically remember for you when rows were inserted.
You must have a column in your table for this, with smalldatetime or
datetime as datatype and a default of CURRENT_TIMESTAMP. See example
below:

CREATE TABLE abc
( ......
, ......
, InsertedAt smalldatetime NOT NULL DEFAULT CURRENT_TIMESTAMP
, .....
)
go

If you have such a column (or another column that provides a dependable
way to find in what order your rows were inserted), you can get the 20
most recent new rows with

SELECT TOP 20 Column1, Column2, ...., ColumnN
FROM abc
ORDER BY InsertedAt DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[siatki]

thx for answer. I thing that it is good solution. It works.
Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:b3********************************@4ax.com...

On Mon, 21 Feb 2005 22:03:23 +0100, siatki wrote:

Hello,
Firstly, sorry for my english.
I have problem with creating SQL statement. I am beginner and I think that
it is very easy to do. Look -
I have to get only the last 20 records from table ABC (f.eg.) and
according
to that how to create the SQL statement? F.eg. SELECT name, forename, date
FROM abc WHERE....
You know - how to finish the statement to get only 20 records lastly
inserted into the table?
Thanks and I am waiting for helpfull answers.

Hi siatki,

SQL Server won't automatically remember for you when rows were inserted.
You must have a column in your table for this, with smalldatetime or
datetime as datatype and a default of CURRENT_TIMESTAMP. See example
below:

CREATE TABLE abc
( ......
, ......
, InsertedAt smalldatetime NOT NULL DEFAULT CURRENT_TIMESTAMP
, .....
)
go

If you have such a column (or another column that provides a dependable
way to find in what order your rows were inserted), you can get the 20
most recent new rows with

SELECT TOP 20 Column1, Column2, ...., ColumnN
FROM abc
ORDER BY InsertedAt DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)