opposite of SELECT TOP?

Is there an opposite of the TOP keyword in a select statement?

I have the following query which works fine but it's not in the order I need because I have to use DESC to get the last 40 records.

select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime
from product
where scarfcode = 'V3'
order by creationtime desc

I need the returned records ordered by creationtime but not descending.

RandyDoes this work

select * from
(
select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime
from product
where scarfcode = 'V3'
order by creationtime desc
)
order by creationtime asc|||Have you tried:

select slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime
from (select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime
from product
where scarfcode = 'V3'
order by creationtime desc) a
order by creationtime|||and.......what's wrong with that?|||Dang it all. Sniped. Why did I go back and replace "select *"? But, Silas missed one important piece. I will leave it to the interested reader to spot it.|||select * from
(
select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime
from product
where scarfcode = 'V3'
order by creationtime desc
)
order by creationtime asc

Tried this and get:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'order'.

I probably should mention this is an old machine I'm running the query against, it's running 7.0. I'm thinking I may just have to do two queries... One to create a temporary table and one to pull the data out of the temporary table in the right order.

Randy|||or you could use Matt's

In any case, you're missing a name for the derived table|||I probably should mention this is an old machine I'm running the query against, it's running 7.0. I'm thinking I may just have to do two queries... One to create a temporary table and one to pull the data out of the temporary table in the right order.As Brett pointed out, just use the code provided by MCrowley. It will work in MS-SQL 7.0 (and subsequent releases).

-PatP|||mcrowley's won't work

the outer SELECT includes the expression left(productname,6)

the derived table has no such column

:)

fix this small error and it will work fine|||Thanks guys! Working great now!

Randy|||Yep. I cutted and pasted. Did not even notice the left function in there. The poster is happy, though.