Dan,
>Hmmm...that wouldn't work, that would only give you the very first one. You'd need a group by clause to make it work properly. Here's an example using the Northwind.Orders table:
>
>select freight, orderdate from orders
>where orderid in
>(select min(orderid) from orders
>group by datepart(yy,orderdate))
I'm trying to remember the correct correlated subquery to use, which I once got from the Advanced SQL book on your bookshelf at EPS. Had to use that to find the most recent invoice for each customer or something like that.
There were two examples there of how to accomplish it, with one using MAX and the other with a having clause, but I just cannot come up with it today for some reason. VFP8 keeps giving me "Command is missing required clause" or "Function name is missing )" (when it is not missing) on everything I tried so far. :-)
Surely someone knows the answer to the original question (to do it in one select) and will chime in here.