>>select LH.* from LocationHistory LH inner join (select Max(ActionDate) as MaxDate,
>> PartID, SerialNo from LocationHistory group by PartID, SerialNo ) Derived on
>> LH.ActionDate = Derived.MaxDate and LH.PartID = Derived.PartID and LH.SerialNo = Derived.SerialNo


This is almost there, except I get back rows 2292, 2924, and 2923.




>>Thanks,
>>
>>Here's what's in the data:
>>
>>

>>HistoryId PartId SerialNo   ActionDate                          Sequence
>>----------------------------------------------------------------------------------------------
>>2920	      2       Serial 1     2009-05-11 14:46:09.407	           1
>>2923	      2       Serial 1     2009-05-11 14:47:05.767                2
>>2921	      3	       Serial 2     2009-05-11 14:46:09.420                1
>>2924	      3	       Serial 2     2009-05-11 14:47:05.860                2
>>2927	      3	       Serial 2     2009-05-11 14:46:09.000                3
>>2922	      4	       Serial 3     2009-05-11 14:46:09.447	           1
>>

>>
>>I tried this
>>
>>

>>
>>SELECT * 
>>	FROM LocationHistory lh1 
>>	WHERE ActionDate = 
>>		(SELECT MAX(ActionDate) 
>>			FROM LocationHistory lh1
>>			WHERE lh1.HistoryId = lh1.HistoryId)
>>

>>
>
>What is your SQL Server version? For me now the ROW_NUMBER() solution is the easiest or
>
>

>select LH.* from LocationHistory LH inner join (select Max(ActionDate) as MaxDate,
> PartID, SerialNo from  LocationHistory  group by PartID, SerialNo ) Derived on
> LH.PartID = Derived.PartID and LH.SerilNo = Derived.SerialNo and LH.ActionDate = Derived.MaxDate 
>

>
>>and got back only row 2924. I also tried
>>
>>

>>SELECT * 
>>	FROM LocationHistory lh1 
>>	WHERE SerialNo = 
>>		(SELECT MAX(SerialNo) 
>>			FROM LocationHistory lh1
>>			WHERE lh1.HistoryId = lh1.HistoryId)
>>

>>I got back only row 2922.
>>
>>
>>What I want back is rows 2923, 2927 and 2922, because those rows are the max of each PartNo/Sequence combo.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>>>I have a table with PartNo, SerialNo and Sequence. I need the get max each row that is that last sequence for a given PartNo/SerialNo combination.
>>>>
>>>>Can someone help me with the syntax?
>>>
>>>Alternative solutions please see Re: Group By Thread #1395684 Message #1395698