Returning the First Value in a Group by clause

[qwert62]

I am kind of new to writing sql queries. I am sure this is a very basic question.

How do I return the first value of a field when using a Group by clause. If I link the SQL tables to Access I am able to run the query:

SELECT tblName.Field1, First(tblName.Field2) AS ID
FROM tblName
GROUP BY tblName.Field1;

When I try to use this same query in SQL, I get an error saying First is not a recognized function name.

Can anyone please tell me how to accomplish this.

Thanks in advance for your help.

 

[tlbroadbent]

There is no such thing as First in SQL. Access provides an interesting extension to SQL with the First function but neither ANSI SQL not T-SQL have an equivalent. By definition, record sets are unordered in SQL so First doesn't make sense.

About as close as you can come is to have a counter, a date or some other "order" indicator on the table and then select the MIN value. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.