Get the last 100 records

My sql database table gets filled automatically.

Every record gets a current date/time stamp.

I want to select the last 100 records, ordered by the date/time stamp.

The newest records should be the last record in the 100 recordset.

How can I do this?

select id, createdon from
(select top 100 id, createdon
from table
order by createdondesc) a
order by createdon

|||

I get an incorrect syntax error on the last ")":

SELECT

DT, VALUE

FROM

(SELECTTOP 10

DT, VALUE

FROM [CAS SHORT HISTORY]

ORDERBY DTDESC)

|||

Make sure you've given the derived table an alias. Here's a working example:

declare @.table1table (idint identity (1,1), createdondatetime)declare @.startdatedatetimedeclare @.enddatedatetimeset @.startdate ='20060101'set @.enddate ='20070101'while @.startdate < @.enddatebegininsert @.table1values (@.startdate)set @.startdate = @.startdate + 1endselect id, createdonfrom (select top 100 id, createdonfrom @.table1order by createdondesc) aorder by createdon

|||

Hi ca8msm,

You are filling a new table, but I'm having a table [CAS SHORT HISTORY] that is already filled, how should I create an alias for this table?

|||

The above is just an example table. Use the query at the bottom and change the table and field names.

|||

select id, createdonfrom
(select top 100 id, createdon
from @.table1
order by createdondesc) a
order by createdon

What is the "a" doing?

|||

It's creating an alias for the derived table.

|||

Bingo! Found it!

SELECT

*

FROM(SELECTTOP 10

DT

FROM

[CAS SHORT HISTORY] CASALIAS

ORDERBY

DT

DESC)

CASALIAS

ORDERBY

DT

ASC