Eliminate rows with duplicate values in a certain column, select one based on another column

I have a table with many columns. The table includes an address_ID column and a type_ID column.

There are some duplicates in the address_ID column, which become apparent when I sort on that column.

I want to create a new table which has no duplicate address_ID rows.

The order of picking which row to select, when there are multiple rows with the same address_ID, goes like this:

1. Select row with type_ID 2869
2. If none, then select row with type_ID 4162
3. If none, select row with type_ID 2868
4. If none, select row with type_ID 4919
2. If none, give up and just select any row

What SELECT statement would give me this result?
deleydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let me know if this floats your boat -->  SQL Server Delete Duplicate Rows

In your case though you'll have to add a CASE block to get the custom priority correct.  Something like this <untested total air code>..
;with a as (
   SELECT address_id, 
       CASE type_ID WHEN 2869 THEN 1 WHEN 4162 THEN 2 WHEN 2868 THEN 3 WHEN 4919 THEN 4 ELSE 5 END as type_id_custom, 
   FROM your_table
  )
b as (
   SELECT address_id, row_number() OVER (partition by address_id, ORDER BY type_id_custom) as row_number
   FROM a
SELECT 
DELETE FROM b WHERE row_number > 1

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Try something like this (caution: air code)

WITH cteAddress AS
(
	SELECT address_ID, [type_ID], A.TypePrecedence,
		ROW_NUMBER() OVER(PARTITION BY address_ID ORDER BY A.TypePrecedence) AS RowNumber
	FROM myTable
	CROSS APPLY
	(
		SELECT address_ID,
			CASE [type_ID]
				WHEN 2869 THEN 1
				WHEN 4162 THEN 2
				WHEN 2868 THEN 3
				WHEN 4919 THEN 4
				ELSE 99
			END AS TypePrecendence
		FROM myTable
	) AS A
)
SELECT address_ID
FROM cteAddress
WHERE RowNumber = 1

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<above comment was edited after original submission
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
merely to demonstrate that you can use a case expression directly within the row_number(), and that use of a CTE isn't essential:
SELECT
      d.*
-- INTO NewTable
FROM (
      SELECT
            t.*
          , ROW_NUMBER() OVER (PARTITION BY t.address_ID 
                              ORDER BY
                                  CASE t.type_ID
                                        WHEN 2869 THEN 1
                                        WHEN 4162 THEN 2
                                        WHEN 2868 THEN 3
                                        WHEN 4919 THEN 4
                                        ELSE 99
                                  END ASC
            ) AS RowNumber
      FROM YourTable AS t
     ) AS d
WHERE RowNumber = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
hi..
Can you try this

INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
FROM TableA A
LEFT JOIN TableB B
ON A.address_ID  = B.address_ID
WHERE B.address_ID  IS NULL
0
deleydAuthor Commented:
Thank you everyone.

Paul's code worked out of the box. (The first two had problems, though they appear to be similar.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.