Field value comparison

Hi,
I have a table with an order no field and an orderdate field. The order field is a far char 50 and the order date Is datetime.  I would like to be able to check to see if there are two  or more of the same orderno  and if so delete all but the most recent of the entries.  
Any help on this would be appreciated
Thanks
Morpheus7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You can easily do this with a window function, like this:
with oo as (
 select *, row_number() over(partition by orderNo order by orderDate DESC) rn
 from orders)
delete from oo where rn > 1

Open in new window

0
Chris LuttrellSenior Database ArchitectCommented:
Use a CTE and the OVER clause to assign ordered values to each value and then delete those that are not the latest one per Order No.
CREATE TABLE aTable (Order_No INT, Order_Date DATETIME);
GO

INSERT INTO dbo.aTable ( Order_No, Order_Date ) VALUES  ( 100, GETDATE() ), ( 100, GETDATE() - RAND() * 50 );
INSERT INTO dbo.aTable ( Order_No, Order_Date ) VALUES  ( 111, GETDATE() ), ( 111, GETDATE() - RAND() * 50 ), ( 111, GETDATE() - RAND() * 50 ), ( 111, GETDATE() - RAND() * 50 );
INSERT INTO dbo.aTable ( Order_No, Order_Date ) VALUES  ( 222, GETDATE() ), ( 222, GETDATE() - RAND() * 50 ), ( 222, GETDATE() - RAND() * 50 ), ( 222, GETDATE() - RAND() * 50 ), ( 222, GETDATE() - RAND() * 50 );
INSERT INTO dbo.aTable ( Order_No, Order_Date ) VALUES  ( 333, GETDATE() ), ( 333, GETDATE() - RAND() * 50 ), ( 333, GETDATE() - RAND() * 50 );
INSERT INTO dbo.aTable ( Order_No, Order_Date ) VALUES  ( 444, GETDATE() );

SELECT * FROM dbo.aTable AT;

WITH cte AS 
(
SELECT AT.Order_No, AT.Order_Date, ROW_NUMBER() OVER (PARTITION BY AT.Order_No ORDER BY AT.Order_Date DESC) rn 
FROM dbo.aTable AT
)
DELETE
FROM cte
WHERE cte.rn > 1;

SELECT *
FROM dbo.aTable AT;

DROP TABLE dbo.aTable;

Open in new window

This is the results:Query Results
0
Chris LuttrellSenior Database ArchitectCommented:
I am too fancy, someone was quicker with less details :)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

chaauCommented:
Did it in SQLFiddle
0
Scott PletcherSenior DBACommented:
Don't think you need to do all that row numbering/counting (1 table scan instead of 2):


DELETE FROM ot
FROM orderstable ot
INNER JOIN (
    SELECT [order no], MAX(orderdate) AS orderdate
    FROM orderstable
    GROUP BY [order no]
    HAVING MIN(orderdate) <> MAX(orderdate)
) AS orders_last ON
    ot.[order no] = orders_last.[order no] AND
    ot.orderdate < orders_last.orderdate
0
chaauCommented:
@Scott: Your query is good, but it will leave duplicate records if there are multiple records with the same max(orderDate) for the orderNo
0
Chris LuttrellSenior Database ArchitectCommented:
Scott, I did a profile comparison of my CTE and your nested select and I only see 1 table scan for my solution and 2 table scans with yoursProfile Comparison
0
Scott PletcherSenior DBACommented:
Sorry, quite true if there's no covering index for [order no] and orderdate.  I shouldn't have assumed that.

I don't object to your ROW_NUMBER() method anyway since you used:
SELECT AT.Order_No, AT.Order_Date, ROW_NUMBER() OVER
and not
SELECT *, ROW_NUMBER() OVER ...
0
Scott PletcherSenior DBACommented:
chaau:

Yeah, I figured you wouldn't get exact same times for the same order no.  If that's possible, use the CTE ... and check your order logic, because you've got a bigger issue than this DELETE!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
check your order logic, because you've got a bigger issue than this DELETE!
:-)
True!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.