April 21, 2009 at 12:22 pm
I have a SQL Server 2005 table that has multiple rows for one specific item, say item 123456. There is a field (call it recID) that has a specific value that is set when this item was added to the table and shold never be changed when a new row is added for this item in this table. I am now finding that recID has a different value from the original. I am trying to write SQL code to return me all occurrences when recID is different for a specific item. Any thoughts.
I am more or less trying to find duplicates.
April 21, 2009 at 12:37 pm
If you only care about duplicates, and not whether the value changed or not, then something like this should work:
select itemID, recID
from mytable
group by itemID, recID
having count(1) > 1;
April 21, 2009 at 12:56 pm
Can you post some DDL and sample data as suggested in the links in my signature line?
Here's a weak attempt to help:
;With cteDupes AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY item_id) AS row_id,
item_id,
rec_id,
FROM
TABLE
)
SELECT
*
FROM
cteDupes AS CD
WHERE
EXISTS (SELECT 1 FROM cteDupes AS CD1 WHERE CD1.row_id > 1 AND CD.item_id = CD1.item_id)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply