I've made a copy of vdvInventoryDistribution and I'm trying to add a column to it called say, to be descriptive, OldestTranDateForThisLotNo.

Suppose every record has a LotNo.  Most LotNos appear in multiple records.  I want to add a column that will indicate the oldest TranDate for each group of LotNos.  I don't want to filter records or aggregate records; I want all records to be returned.  I just want the new column to contain the oldest TranDate for each group of LotNos.  In other words, if there are four records with the same LotNo, the new column will include the same date for each record...the oldest TranDate for that group.  If a LotNo only appears in one record, that's fine, the new column would just have the TranDate of that one transaction.

I think this might be possible by using the MIN function along with some other SQL sorcery.

Using 500 7.6.6 and SQL 2008R2.  Any ideas?