First statement from Access to SQL

I'm trying to convert this from Access to SQL:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL

But it won't accept the First statement, specifically "First" is not recognized.

First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC

Does anyone know a way around?I'd test this in the SQL Anaylyser:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, Top(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL|||Let a brother know if it works

And remember

The RedSkins are back and Championship will follow.|||Understand that the concept of first and last doesn't have any meaning

Don't know how access doesn't...but you can do

SELECT ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
GROUP BY ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7)

Or

SELECT TOP 1 ORDER_NUMBER
, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
, MAX(ITEM_DESC) AS MAX_ITEM_DESC
FROM ORDER_DETAIL
ORDER BY SUBSTRING(ITEM_NUMBER,2,7)

You need to know if you want the MAX or MIN, or to order by something

The order of data in a database has no meaning...

Go Ellie!

PS> Another fluf post on my part...damn...|||well, i wouldn't exactly call it a fluf post, but your second code example is wrong, it has a non-aggregate expression in the SELECT list along with the MAX aggregate, but it's lacking the GROUP BY clause

:cool: