SQL 2005 - Displaying distinct data and retrieving the min value of a child record

Using SQL 2005, I find myself stuck in solving this problem.

My Tables:

And many many more columns

PrimaryContact (Integer Field: 1 would be the highest, 2 next in line, etc.  0 would be never selected, so last)

The problem is that when I do a query like the following:

Select C.ID, A.* from Accounts A
LEFT JOIN Contacts C ON (C.AccountsID = A.ID)

Then if many contacts exist then I get many results.  However, I don't just want any Contact record.  I need to get the contact with MIN(PrimaryContact) with 0 being last.  So the min would get 1 then 2 then 3, etc. and then 0.

This is the query that I would have thought would have worked, but it won't let me do the order by in a sub query.

Select A.ID, C.PrimaryContact, C.ID  from Accounts A
LEFT JOIN Contacts C ON (C.AccountsID = A.ID) AND (C.ID = (Select CT.ID FROM Contacts CT WHERE CT.AccountsID=A.ID ORDER BY MIN(PrimaryContact) ))

Of course I would have to have something like this: CASE WHEN CT.PrimaryContact = 0 THEN 999 ELSE CT.PrimaryContact END

Another way of doing it would be with the Group By A.ID, but I have so many columns, I don't want to list them all out, hopefully their is a better way.
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.

Select C.ID, A.* from Accounts A
LEFT JOIN Contacts C ON (C.AccountsID = A.ID)
ORDER BY CASE MIN(CT.PrimaryContact) WHEN  0 THEN 99999999999 ELSE CT.PrimaryContact END
perkleyAuthor Commented:
They always sound good, but I never get any of them to work.  This is what I get from your query (after converting the CT.PrimaryContact to C.PrimaryContact)

Column 'Contacts.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
Select A.ID, C.PrimaryContact, C.ID  
from Accounts A
LEFT JOIN Contacts C 
  ON C.AccountsID = A.ID
  AND C.ID = (Select TOP 1 CT.ID 
               FROM Contacts CT 
               WHERE CT.AccountsID=A.ID 
              ORDER BY CASE WHEN CT.PrimaryContact = 0 THEN 1 ELSE 0 END ASC
                , CT.PrimaryContact ASC

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
perkleyAuthor Commented:
Thank you very much, I have tried so many different things.  You are a life saver.
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.