Select distinct rows from multiple column query

Question

I have this query that currently returns all the pertinent information I need, but I need it to only return unique DISPLAYCLAIMNUMBER rows. I have tried throwing a DISTINCT before it, but that gives me an error for invalid syntax. I'm pretty confused on how to put DISTINCT on something that is in the middle of my SELECT. The order of my query is also important, because I'm sending the data to an Excel table. I'm sure this is an extremely inefficient, but the database is very fragmented, and I have to refer to several tables, just for one piece of data. Feel free to improve, if there is a way to.

SELECT cd.NAMECREFID, clm.CONTACTNAME, cd.ASSIGNEDPOLICY, clm.DISPLAYCLAIMNUMBER, clm.CAUSEOFLOSS, scp.PERILCODE, scp.DESCRIBE, clm.ASSIGNEDDATETIME, clm.CLOSEDATETIME, clp.PAYMENT
FROM CLMSTAT clm
inner join SCPERTBL scp on clm.ASSIGNEDPERILNUMBER = scp.PERILCODE
inner join CLMPYMT clp on clm.ASSIGNEDCLAIMNUMBER = clp.ASSIGNEDCLAIMNUMBER 
inner join SNAMES sn on clm.CONTACTNAME = sn.NAME
inner join TCUSDTL cd on sn.NAMEID = cd.NAMEID
WHERE clm.CONTACTNAME Like '%Smith%'
ORDER BY clm.CONTACTNAME, clm.ASSIGNEDDATETIME DESC

Is DISPLAYCLAIMNUMBER unique in CLMSTAT or are there duplicates there. If it is unique then one of your joins are creating duplicates in the result - first step is to figure out which one and eliminate them there. — Scott Mildenberger
– Scott Mildenberger, Commented May 5, 2016 at 22:29

DhruvJoshi · Accepted Answer · 2016-05-05 22:18:34Z

2

You should simply try this

SELECT 
DISTINCT -- at the start
cd.NAMECREFID, clm.CONTACTNAME, cd.ASSIGNEDPOLICY, clm.DISPLAYCLAIMNUMBER, clm.CAUSEOFLOSS, scp.PERILCODE, scp.DESCRIBE, clm.ASSIGNEDDATETIME, clm.CLOSEDATETIME, clp.PAYMENT
FROM CLMSTAT clm
inner join SCPERTBL scp on clm.ASSIGNEDPERILNUMBER = scp.PERILCODE
inner join CLMPYMT clp on clm.ASSIGNEDCLAIMNUMBER = clp.ASSIGNEDCLAIMNUMBER 
inner join SNAMES sn on clm.CONTACTNAME = sn.NAME
inner join TCUSDTL cd on sn.NAMEID = cd.NAMEID
WHERE clm.CONTACTNAME Like '%Smith%'
ORDER BY clm.CONTACTNAME, clm.ASSIGNEDDATETIME DESC

This will give you a unique combination of all columns in the SELECT list.

However if this is not what you desire, and you simply want unique values for clm.DISPLAYCLAIMNUMBER only, I'd ask why do you need rest? If you don't then your query's SELECT list should be like below

SELECT DISTINCT clm.DISPLAYCLAIMNUMBER
FROM CLMSTAT clm 
...

answered May 5, 2016 at 22:18

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tangwheeler Over a year ago

I need the rest for data analysis purposes, but this seems to have done what I wanted. I was under the impression that everything would be affected by DISTINCT. For example, only one row per CONTACTNAME would display, but that's not the case, it seems. Thanks for your help!

C. Price · Accepted Answer · 2016-05-05 22:47:59Z

0

SELECT cd.NAMECREFID, clm.CONTACTNAME, cd.ASSIGNEDPOLICY, clm.DISPLAYCLAIMNUMBER, clm.CAUSEOFLOSS, scp.PERILCODE, scp.DESCRIBE, clm.ASSIGNEDDATETIME, clm.CLOSEDATETIME, clp.PAYMENT
FROM CLMSTAT clm
inner join SCPERTBL scp on clm.ASSIGNEDPERILNUMBER = scp.PERILCODE
inner join CLMPYMT clp on clm.ASSIGNEDCLAIMNUMBER = clp.ASSIGNEDCLAIMNUMBER 
inner join SNAMES sn on clm.CONTACTNAME = sn.NAME
inner join TCUSDTL cd on sn.NAMEID = cd.NAMEID
WHERE clm.CONTACTNAME Like '%Smith%'
GROUP BY clm.DISPLAYCLAIMNUMBER
ORDER BY clm.CONTACTNAME, clm.ASSIGNEDDATETIME DESC

edited May 5, 2016 at 22:47

answered May 5, 2016 at 21:46

C. Price

237 bronze badges

2 Comments

Tangwheeler Over a year ago

I've tried multiple variations of this. It seems to always throw a syntax error. Your exact example does as well.

Scott Mildenberger Over a year ago

To use GROUP BY you need to aggregate all fields not in the GROUP BY.

Collectives™ on Stack Overflow

Select distinct rows from multiple column query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related