Solved

Field value comparison

Posted on 2014-09-30
10
128 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 125 total points
ID: 40353699
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 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 125 total points
ID: 40353704
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 27

Expert Comment

by:Chris Luttrell
ID: 40353706
I am too fancy, someone was quicker with less details :)
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 25

Expert Comment

by:chaau
ID: 40353718
Did it in SQLFiddle
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40355375
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
 
LVL 25

Expert Comment

by:chaau
ID: 40355572
@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 27

Expert Comment

by:Chris Luttrell
ID: 40355598
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:Scott Pletcher
ID: 40355688
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:
Scott Pletcher earned 250 total points
ID: 40355694
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 27

Expert Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 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