We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Select unique/distinct for singular column?

brassmonkeyboy
on
Medium Priority
322 Views
Last Modified: 2009-12-16
Here is the SQL query I'm working on:

SELECT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, [Insured].[LastName] + ' v. ' + [Claimant].[LastName] AS CaseTitle, [CaseInfo].[CaseID]
FROM [CaseInfo], Attorney, Insured, Claimant, Insurer
WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

There are multiple claimants and insured parties for each CaseID.  I want to return only ONE result per each unique CaseID.  How can I do this?  I've experiemented with Distinct and Unique to no avail, but I'm new at SQL so I'm sure I just missed something.
Comment
Watch Question

Commented:
when you have multiple claimants and insured parties related to a caseID which one do you want to display?
may be you have to use group by and get min or max from claimants and insured.
it helps if you can give some sample data and the results you are looking for.

try this and see

SELECT [CaseInfo].[CaseID], Max( LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1)) AS Attorney,
Max([Insured].[LastName] + ' v. ' + [Claimant].[LastName]) AS CaseTitle, [CaseInfo].[CaseID]
FROM [CaseInfo], Attorney, Insured, Claimant, Insurer
WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)
group by [CaseInfo].[CaseID]

Top Expert 2006
Commented:

SELECT       [CaseInfo].[CaseID],
      LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney,
      ( select top 1 lastname  from Insurer where [Insurer].[InsurerID] = [Caseinfo].[InsurerID] order by lastname) + ' v. '
      + ( select top 1 lastname  from Claimant where [Claimant].[CaseID] = [Caseinfo].[CaseID] order by lastname) AS CaseTitle,
      [CaseInfo].[CaseID]
FROM
      [CaseInfo]
      inner join  Attorney
      on [Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]
      inner join  Insured
      on [Insured].[CaseID] = [Caseinfo].[CaseID]
      
WHERE


       [Caseinfo].CaseID = 1

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2011

Commented:
i find your design confusing... shouldn't the claimant also have relations with Insurers...
and indeed many insueres could be involved on both sides dependant on policy coverage / periods of indemnity...
please confirm the relation ships between caseinfo and the other tables...

i'd also expect some indicator for the principal opponnents to be made somewhere...

hth

SELECT C.[CaseID]
      , LEFT(A.[FirstName],1) + LEFT(A.[MiddleName],1) + LEFT(A.[LastName],1) AS Attorney
      , I.[LastName] + ' v. ' + Clm.[LastName] AS CaseTitle
      , C.[CaseID]
 FROM [CaseInfo] as C
inner Join Attorney as A
   on A.[AttorneyID] = C.[HeadAttorneyID]
Inner Join Insured as I      
   on I.[CaseID] = C.[CaseID])
Inner Join Insurer as Ins
   on Ins.InsurerID = C.InsurerID
Inner Join (select caseid,min(lastname) as lastname from Claimant group by caseid)  as Clm
   on Clm.[CaseID] = C.[CaseID]

WHERE C.CaseID = 1

 

Author

Commented:
I honestly don't care which of the names to use.  I just need one from each.

The insurer is used in different forms of the query (this is being generated in VB.NET, and the query is different based on the data input.)  So it's only there because it's needed SOMETIMES.

Imran fast, I used a form of what you gave me ("SELECT TOP 1"), but it seems that I have to include every single field in the SELECT statement in the Group By list.  Is this true?

Here's my query:

SELECT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, (SELECT TOP 1 [Insured].[LastName] FROM Insured WHERE Insured.CaseID = CaseInfo.CaseID) + ' v. ' + (SELECT TOP 1 [Claimant].[LastName] FROM Claimant WHERE Claimant.CaseID = CaseInfo.CaseID) AS CaseTitle, [CaseInfo].[CaseID]

FROM [CaseInfo], Attorney, Insured, Claimant, Insurer

WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

GROUP BY CaseInfo.CaseID

Author

Commented:
I just added all the select fields into the group by and then changed "SELECT" to "SELECT DISTINCT" and it worked!

SELECT DISTINCT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, (SELECT TOP 1 [Insured].[LastName] FROM Insured WHERE Insured.CaseID = CaseInfo.CaseID) + ' v. ' + (SELECT TOP 1 [Claimant].[LastName] FROM Claimant WHERE Claimant.CaseID = CaseInfo.CaseID) AS CaseTitle, [CaseInfo].[CaseID]

FROM [CaseInfo], Attorney, Insured, Claimant, Insurer

WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

GROUP BY CaseInfo.CaseID, Attorney.FirstName, Attorney.MiddleName, Attorney.LastName, Insured.LastName, Claimant.LastName, Insured.CaseID, Claimant.CaseID

Commented:

its confusing, your final sql looks like the sql i posted and you accepted other answer as solution

Author

Commented:
The only reason my query looks like what you posted is because your suggestion was a modification of the original query <i>I</i> posted.  None of the changes you made were used because they didn't work.  The methods suggested in the awardee's suggestion were used with success.  Hence, he is awarded the points.

Commented:

No problem,
in the answer accepted and in the actual query in your question there is no group by and the final query you posted is using group by clause as suggested in my query, that cause the confusion

Author

Commented:
Gotcha.  THe GROUP BY was added because of an error that showed up.  The part that ehlped me was the (SELECT TOP 1).
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.