FIRST in SQL

[Onyxpurr]

I am converting my ACCESS queries to SQL stored procedures.

I have one query, in ACCESS, that uses the FIRST function. In essence it takes the first occurance of one field based on a grouping of another.

e.g.
FIRST OCCURANCE FOR
10/20/2003 BILL SMITH
10/23/2003 JOE SCHMOE

How can I accomplish the same results in SQL?

Thanks!!!!

 

[swampBoogie]

You can't. There is no concept of first in a relational database. You can, for instance, get the record with the lowest data within each group

select * from t as q
where dateColumn = (
select min(dateColumn)
  from t
 where t.groupingColumn = q.groupingColumn)

 

[SQLSister]

If, however, you use an order by to sort the records then you can use the min() function to pick the first one in that specific order.

So
select min(RecordDate) from table1 order by RecordDate Asc would get you the earliest date a record was inserted.

BUt there is no such thing as first as the records are not necessarily stored in the order you inserted them, especially if you have a clustered index. You must set up your own way to tell which order teh records were inserted in. This is usually done through either a date field of some type or an identity field.

 

[Onyxpurr]

Good point!!! I'll look for an autonumber field in the table and capture the min record for that. Thanks!!!