Upsizing Access 97 to SQL server 6.5 Part IV

I have come across another problem when upsizing my database to SQL from Access. This one pertains to a select statement. This is the code I used to extract the last record in a table.

Code:

<cfquery datasource="DataSourceName" name="QueryName">
SELECT LAST(TableName.FieldName) AS LastOfFieldName FROM TableName
</cfquery>

As you can see I used the Last function and this works fine in Access, but SQL server doesn’t recognise this statement and returns an error. So I had to use a slightly more convoluted way to get the last record.

Code:

<cfquery datasource="DataSourceName" name="QueryName" maxrows="1">
Select Tablename.FieldName From TableName
Order by TableName.FieldName DESC
</cfquery>

This query selects all the fields and orders them in descending order. You then use the maxrows= statement to limit the output to the first field it retrieves.