Are there aggregate functions for MS SQL Server like 'First' or 'Last'?

>There is no "getting around" in this case.
>
>You must place all the column names in the group by clause.
>
>PF
>
>>This should be easy for seasoned c/s pros but I have problem getting around it. In MSSQL, columns that appear in the select list "must be" used as arguments of an aggregate function in the GROUP BY clause. For example, in VFP you could do this:
>>
>>SELECT table1.*, table2.field1, table2.field2, table2.field3
>> FROM table1 JOIN table2 ON table1.fieldx=table2.fieldx
>> GROUP BY table2.field2
>>
>>But you can't do this with MSSQL. How do I get around this "feature"?