Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Still getting Multiple records in query

Status
Not open for further replies.

Amesville

Programmer
Oct 10, 2011
93
US
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?

Code:
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
 
Amesville said:
A few weeks ago...I received...
For thos interested:
Amesville said:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top