Still getting Multiple records in query

[Amesville]

Hi Folks

A few weeks ago I had asked how to create a select statement that would only return one record per ID Number where the Timestamp was the latest of many. I received some very good advice (Thank You) from this group.

However, it seems that there are some records with the same timestamp (down to the thousandth of a second) and when this occurs I still get two records for the same ID. If the records were completely identical it would not be a problem (would still return only one record) but one of the fields has a different value, which makes it a unique record.

My statement is below -- how can I change this to get only one record even when the timestamp is exactly the same?

SELECT DISTINCT MI.Timestamp, MI.ParcelNumber, MI.SSCC18, MI.CustomerNumber, MI.CarrierID, 
	MI.TransMethod, MI.Hazmat, MI.ExpectedWeight, MI.ActualWeight, ISt.PctVariation, CAST(MI.Lane AS INTEGER) - 1000, MI.ReasonCode, '2' AS WeightStation
	FROM whtbSorterProductHistory MI 
JOIN (SELECT DISTINCT ParcelNumber ParcelNum, MAX(Timestamp) MaxTime 
	FROM whtbSorterProductHistory group by ParcelNumber) MaxItem
ON MI.ParcelNumber = MaxItem.ParcelNum AND MI.Timestamp = MaxItem.MaxTime
LEFT OUTER JOIN whtbSorterInductStatus ISt 
ON MI.parcelNumber = ISt.ParcelNumber
WHERE MI.SorterName = 'ShippingSorter'
AND ISNUMERIC(ISt.ParcelNumber) = '1'

Thanks

Craig

 

[PDreyer]

A few weeks ago...I received...

For thos interested:

http://www.tek-tips.com/viewthread.cfm?qid=1749325

it's very unlikely there would be two scans with the same time.

So now that the unlikely event happened how can you tell from your data which value is the last one inserted?
Do you have a unique key on whtbSorterProductHistory?
Do you perhaps have an identity column on table whtbSorterProductHistory? Then use max(your_identity_column) instead of timestamp.