One Name Per ID

I have a table like this:

BranchNumber  BranchName                  IDField
1900                     AMP123                          1234
1900                     American 123                 6549
1900                     Am 1 2 3                          98651
1901                     Jones and Company      32
1901                     Jones Co                           1656
1901                     Jones Co. Inc.                   65749

I want to pull one BranchName for each Branch Number.  Using the IDField to pull the Name of the most recent ID

Result:
BranchNumber  BranchName
1900                     Am 1 2 3  
1901                     Jones Co. Inc.  

Using SQL Server 2012.
Thanks for all help!
Scott
Scott WilliamsData AnalystAsked:
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:
Good luck with that, and by that I mean what is your criteria for determining if BranchName is a duplicate?  

For example, if there's a Jones Co. in Reno, Nevada, and a Jones Co. Inc. in New York City, then they are really unique branches and not duplicates.

Are there other fields anywhere that can be used to determine duplicates besides BranchName ?
0
Saurabh Singh TeotiaCommented:
You can use this query...

select tb1.branchnumber,tb2.branchname
from table1 tb2
inner join
(select branchnumber,max(idfield) as id
from table1
group by branchnumber) tb1 on tb1.id=tb2.idfield and tb1.branchnumber=tb2.branchnumber

Open in new window


Saurabh...
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
Scott PletcherSenior DBACommented:
From SQL 2005 on, ROW_NUMBER() is usually much better for that type of request:

SELECT BranchNumber, BranchName, IDField --,...
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY BranchNumber ORDER BY IDField DESC) AS row_num
    FROM table_name
) AS derived
WHERE
    row_num = 1
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Using the IDField to pull the Name of the most recent ID
Missed that part.  Looks like you have two good answers to work with.
0
Scott WilliamsData AnalystAuthor Commented:
Both solutions are best.  Now to decide.  Thank you very much!
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
Query Syntax

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.