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?
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 Data DudeCommented:
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
DELETE FROM b WHERE row_number > 1

Open in new window

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

Jim HornMicrosoft SQL Server Data DudeCommented:
<above comment was edited after original submission
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
merely to demonstrate that you can use a case expression directly within the row_number(), and that use of a CTE isn't essential:
-- INTO NewTable
          , 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

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 BHOGAYTATeam LeadCommented:
Can you try this

INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
ON A.address_ID  = B.address_ID
deleydSoftware EngineerAuthor Commented:
Thank you everyone.

Paul's code worked out of the box. (The first two had problems, though they appear to be similar.)
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.