>This is hard to explain so I hope you can understand what I am trying to do.
>
>I have a parent table with recordindex, partno fields. This table can have multiple records with same partno.
>
>I have a child table with a recordindex, parentindex, datestamp, description, keycharacteristicvalue fields. This table can have many records of the same keycharacteristicvalue, one for each parent record of the same partno, one catch is the description is not always keyed in the same.
>
>Anyway I need a distinct list of the keycharacteristics with the latest date for a prticular partno along with the description field of each of those records.
>
>Example:
>
>Parent #1 has partno = 'A' with child records having a keycharacteristic = WEIGHT description = weight1 and a keycharacteristic = HEIGHT description = height1 completed on 05/10/2011
>Parent #2 has partno = 'A' with child records having a keycharacteristic = WEIGHT description = wght1 and a keycharacteristic = LENGTH description = lg1 completed on 05/12/2011
>
>So my list would have WEIGHT description wght1 and LENGTH description lg1 from Parent #2 and HEIGHT description height1 from Parent #1

SQL Server 2005+ specific syntax:

;with cte as (select C.*, 
row_number() over (partition by PartNo, KeyCharacteristicValue ORDER BY DateStamp DESC) as Row 
FROM Child C)

select * from cte where Row = 1