Solved

# Field value comparison

Posted on 2014-09-30
107 Views
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
Question by:Morpheus7
• 4
• 3
• 3

LVL 24

Assisted Solution

chaau earned 125 total points
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
``````
0

LVL 26

Assisted Solution

Chris Luttrell earned 125 total points
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;
``````
This is the results:
0

LVL 26

Expert Comment

I am too fancy, someone was quicker with less details :)
0

LVL 24

Expert Comment

Did it in SQLFiddle
0

LVL 69

Assisted Solution

ScottPletcher earned 250 total points
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 24

Expert Comment

@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

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 yours
0

LVL 69

Expert Comment

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

ScottPletcher earned 250 total points
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

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

## Featured Post

### Suggested Solutions

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.