Really complicated select statement

Question

Really complicated select statement

steve.walls

SSC Enthusiast

Points: 133
More actions
February 3, 2010 at 4:16 pm

#137812

Hi,
I have a table in SQL 2005 database that has become contamined with wrong values.
The table contains orders and I need to remove the values if the order numbers and value are the same but the product and serial numbers are different.
IndexID, OrderNo, Value, Product, SerialNo.
1, 12345, 50.00, Stuff, Serialxxx
2, 12345, 50.00, Stuff, Serialxxx
3, 54321, 55.00, Stuff, Serialxyz
4, 54321, 55.00, Stuff, Serialabc
5, 54321, 55.00, Stuff, Serialxyz
How can I achieve this?
Additonal problem.
If an order appears many times and all values match I do not consider that a problem and do not want these records to appear in final table.
Also, in the example above with order 12345 looks good to me as order and value match but so does product and serial.
For Order 54321. I would need to see IndexID 3,4 & 5 in the final table. Not just the matching pair 3 and 5 as it may be 4 that has the correct/wrong value and the others correct/wrong.
Ultimately I would like to have a table that only contains the IndexID of the orders that I suspect are wrong, ie where the OrderNo and Values match but the Product and serialno's do not. But I need all IndexIds for each of these orders.
Any help appreciated.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

This should all be fairly easily achieved by use of the row_number() function.

Once happy that you are able to target the right data , ie all the rows you wish to keep return a row_number() of 1 then wrap it in a cte and delete where row_number <>1.

Hope that makes some sense.

Clear Sky SQL
My Blog[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'm not sure if I'm heading in the right direction...

If you want to see all order numbers with different values then you could use something like the following (untested, since I didn't bother to set up the test data...)

;WITH CTE AS

(

SELECT OrderNo,COUNT(*) as cnt

FROM table

GROUP BY OrderNo, Value, Product, SerialNo

HAVING COUNT(*) > 1

)

SELECT table.*

FROM table

INNER JOIN CTE on CTE.OrderNo = table.OrderNo

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

lmu92 (2/3/2010)
I'm not sure if I'm heading in the right direction...
If you want to see all order numbers with different values then you could use something like the following (untested, since I didn't bother to set up the test data...)
;WITH CTE AS
(
SELECT OrderNo,COUNT(*) as cnt
FROM table
GROUP BY OrderNo, Value, Product, SerialNo
HAVING COUNT(*) > 1
)
SELECT table.*
FROM table
INNER JOIN CTE on CTE.OrderNo = table.OrderNo

Heh... dang it. You beat me to it, Lutz. Here's another take on the problem...

--===== This is just a test table and is not a part of the solution

CREATE TABLE #Test

(

IndexID INT, OrderNo INT, Value DECIMAL(9,2), Product VARCHAR(15), SerialNo VARCHAR(15)

)

INSERT INTO #Test

(IndexID, OrderNo, Value, Product, SerialNo)

SELECT 1, 12345, 50.00, 'Stuff', 'Serialxxx' UNION ALL

SELECT 2, 12345, 50.00, 'Stuff', 'Serialxxx' UNION ALL

SELECT 3, 54321, 55.00, 'Stuff', 'Serialxyz' UNION ALL

SELECT 4, 54321, 55.00, 'Stuff', 'Serialabc' UNION ALL

SELECT 5, 54321, 55.00, 'Stuff', 'Serialxyz'

--===== This is the solution, I think

WITH

cteFindBad AS

( --=== Aggregate distinct counts for Product & SerialNo for each OrderNo/Value

-- and return only those OrderNo/Values with "bad" counts.

SELECT OrderNo,

Value,

COUNT(DISTINCT Product) AS ProdCount,

COUNT(DISTINCT SerialNo) AS SNCount

FROM #Test

GROUP BY OrderNo, Value

HAVING COUNT(DISTINCT Product) > 1

OR COUNT(DISTINCT SerialNo) > 1

) --=== Using the info we found above, find the "bad" ID's

SELECT t.IndexID

FROM #Test t

INNER JOIN cteFindBad fb

ON t.OrderNo = fb.OrderNo

AND t.Value = fb.Value

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve.walls SSC Enthusiast Points: 133 More actions · Answer 4

Thanks all for your contributions. I had never heard of CTE and have since looked this up on technect, very handy sql tool to have around.

I have gone with the post from Jeff (thanks), copied and pasted into my own solution changing some names and it gives me exactly what I am after.

Thanks very much and I learned something new.