SQL Equivalent of FIRST and LAST functions

[Jenns]

Does anyone know the SQL equivalent of the FIRST and LAST aggregate functions from Access.

We are using SQL 7.0 (if it makes a difference) Thanks!
Jenn

 

[jalbao]

Well i know that you can use TOP for FIRST;
as in

SELECT TOP 10 firstname
FROM USERS


i don't know if there is a BOTTOM though... to get the LAST records i'd always do an ORDER BY users DESC along with the TOP function.

 

[bperry]

<albao> is right on, there are no FIRST/LAST/BOTTOM functions, and the situation is often dealt with by using TOP, as he shows.

When using TOP, you will almost always want to add an Order By clause (in fact, there was a big discussion in this forum just the other day about this)

So you might say,
SELECT TOP 1 firstname
FROM Users
Order by firstname

And, as <albao> also points out, you can get the 'last' record by sorting the list in reverse with the DESC modifier.

SELECT TOP 1 firstname
FROM Users
Order by firstname DESC