CODEWITHSUNDEEP
sql-server
Tangwheeler
Tangwheeler

Reputation: 209

Select distinct rows from multiple column query

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

Upvotes: 1

Views: 83

Answers (2)

C. Price
C. Price

Reputation: 23

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

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17146

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 
...

Upvotes: 2

Related Questions