First() to SQL?

Hi,
I'm working with a project translating Access databases to SQL Server.
Can anyone explain the mystic function First() to me?
How can it's function be replaced by SQL?
(I've posted this in the Accessforum also)As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..

http://office.microsoft.com/en-us/assistance/HA010345631033.aspx

In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.|||First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.

In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.

select [YourValue] as FirstValue
from [YourTable]
inner join
(select min([SortKey]) as FirstKey from [YourTable]) Subquery
where [YourTable].[SortKey] = Subquery.FirstKey

If you sortkey is not unique, you will get multiple records in your result.