Tim -- something like this would work (using Northwind as a sample). I assume you want to order it by OrderDate -- i.e., in this example, "first" means "first in time". You could change that to an "last-update" field or an identity column if you want it by entry....
select O.CustomerId,
(select top 1 OSub.OrderId
from ORDERS OSub
where OSub.CustomerID = O.CustomerId
order by OrderDate)
as FirstOrderNum
from Orders O
GROUP BY O.CustomerId
Ugly, I know. But I can't really think of a better way at the moment.
>>>What is the easiest way to select the first record based on a single field?
>>
>>See SELECT TOP 1 ...
>
>I should have given a better explanation of what I am looking for.
>
>If I have several records for companyA and several records for companyB and several for companyC, what is the easiest way to select the first record for each company?
The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser people so full of doubts. - Bertrand Russell
