Solved

Field value comparison

Posted on 2014-09-30
10
107 Views
Last Modified: 2014-10-08
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
Comment
Question by:Morpheus7
  • 4
  • 3
  • 3
10 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 125 total points
Comment Utility
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
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 125 total points
Comment Utility
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
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
I am too fancy, someone was quicker with less details :)
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Did it in SQLFiddle
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 24

Expert Comment

by:chaau
Comment Utility
@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
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
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
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
check your order logic, because you've got a bigger issue than this DELETE!
:-)
True!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL String parsing and fetching certain value 5 45
count vs exists 9 47
Another way of doing this SQL 8 40
Sql Permission 6 42
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now