Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Select unique/distinct for singular column?

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.
0
brassmonkeyboy
Asked:
brassmonkeyboy
1 Solution
 
appariCommented:
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]

0
 
imran_fastCommented:

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
0
 
LowfatspreadCommented:
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

 
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
brassmonkeyboyAuthor 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
0
 
brassmonkeyboyAuthor 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
0
 
appariCommented:

its confusing, your final sql looks like the sql i posted and you accepted other answer as solution
0
 
brassmonkeyboyAuthor 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.

0
 
appariCommented:

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
0
 
brassmonkeyboyAuthor Commented:
Gotcha.  THe GROUP BY was added because of an error that showed up.  The part that ehlped me was the (SELECT TOP 1).
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now