insert distinct record set
-
I have a source table OrderDetail. It contains the Order Number, Order Line, Date Received, etc...
Example:
Order Number Order Line Date Received
1001 01 2006-02-12
1001 02 2006-02-01
1001 03 2006-02-10
I have a target table OrderReceived. It contains only the Order Number and Date Received.
I need to have distinct records for each Order Number with the earliest Date Received
Example:
Order Number Date Received
1001 2006-02-01
Thanks for the help.
-
Try this one:
Select [Order Number], min([Date Received]) From OrderDetail
Lucky -
If I'm understanding you correctly, you want to update the OrderReceived table with the earliest OrderNumber,DateReceived from the OrderDetail table.
Try this:
INSERT INTO OrderReceived (OrderNumber , DateReceived) SELECT TOP 1 OrderNumber, DateReceived FROM OrderDetail WHERE OrderNumber = ?? --your criteria ORDER BY DateReceived
-
Yes, I want to update the OrderReceived table from the OrderDetail table.
I need one record each for the Order Nmber with the earliest Received Date.
Your insert code only returns "One" record.
I need a record for each Order Number.
-
INSERT INTO OrderReceived
(OrderNumber,
DateReceived)
SELECT OrderNumber, min(DateReceived) FROM OrderDetail
GROUP BY OrderNumber
-
>>Yes, I want to update the OrderReceived table from the OrderDetail table.
Update ?
Or Insert ?
Or both, inserting where not already there and updating existing records ?
-
At this time, just "Insert".
INSERT INTO OrderReceived
(OrderNumber,
DateReceived)
SELECT OrderNumber, min(DateReceived) FROM OrderDetail
GROUP BY OrderNumber
This works fine!!! thanks.
But if you can provide sql for doing 'both, inserting where not already there and updating existing records' that would be nice for future reference!!
-
>> for future reference
How far in the future ?
If you'll have upgraded to SQL 2005 by then, you'll be able to use UPSERTUntil then:
-- Insert where not already existing
INSERT INTO OrderReceived
(OrderNumber, DateReceived)
SELECT OrderNumber, min(DateReceived)
FROM OrderDetail As od
WHERE NOT EXISTS (
SELECT * FROM OrderReceived As orc
WHERE od.OrderNumber = orc.OrderNumber
)
GROUP BY OrderNumber
--Update where already present
UPDATE r
SET DateReceived = dt.EarliestDate
FROM OrderReceived As r
INNER JOIN
(
SELECT OrderNumber, min(DateReceived) As EarliestDate
FROM OrderDetail
GROUP BY OrderNumber
) dt
On (dt.OrderNumber = r.OrderNumber)
-- WHERE clause to only update records where there's been a change in date
WHERE dt.EarliestDate <> r.DateReceived
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply