February 3, 2011 at 8:48 am
I have a database that I'm updating to be more streamlined. My table currently consists of:
ID | CODE | MODEL | DEVICE | MANUFACTURER | SUCCESS | ATTEMPTS | EMP_NUM | VOTE_DATE | ADD_DATE
My modified table consists of a new column called FAMILY which groups the models into a family that share the same values. That way I can have one record per family vs one for each individual model. I'll eventually remove the MODEL Column
ID | CODE | MODEL | FAMILY | DEVICE | MANUFACTURER | SUCCESS | ATTEMPTS | EMP_NUM | VOTE_DATE | ADD_DATE
I need to delete the duplicate records that share a common group (CODE | FAMILY | DEVICE | MANUFACTURER). The catch is I want to keep the record with the highest amount of attempts.
I have this simple SELECT statement that shows me the codes I want to keep.
SELECT CODE, FAMILY, DEVICE, MANUFACTURER, MAX(ATTEMPTS)AS ATTEMPTS
FROM codes_table
GROUP BY CODE, FAMILY, DEVICE, MANUFACTURER
I need to translate this into a DELETE statement.
February 3, 2011 at 8:55 am
Maybe:
;WITH AttemptOrder
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY CODE, FAMILY, DEVICE, MANUFACTURER ORDER BY ATTEMPTS DESC) AS RowNum
FROM codes_table
)
DELETE AttemptOrder
WHERE RowNum > 1
February 3, 2011 at 12:15 pm
Perhaps
MERGE INTO codes_table C
USING (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY CODE, FAMILY, DEVICE, MANUFACTURER
ORDER BY ATTEMPTS DESC
) AS Rnum
) FROM codes_table
) R
ON R.CODE = C.CODE AND R. FAMILY = C.FAMILY AND
R.DEVICE = C.DEVICE AND R.MANUFACTURER = C.MANUFACTURER
WHEN MATCHED AND Rnum > 1
THEN DELETE
Tom
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply