Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
0
Morpheus7
Asked:
Morpheus7
  • 4
  • 3
  • 3
4 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Chris LuttrellSenior Database ArchitectCommented:
check your order logic, because you've got a bigger issue than this DELETE!
:-)
True!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now