question

SSGC avatar image
SSGC asked

remove special duplicate from data set

I get a data set from client as below (query just show the data set look like, client give us the csv file like query output): ![alt text][1] CREATE TABLE #A(ID INT, Name VARCHAR(10)) INSERT INTO #A(ID, Name) VALUES(1,'A1'),(2,'A2'),(3,'A3') CREATE TABLE #B(ID INT, Party VARCHAR(10)) INSERT INTO #B(ID, Party) VALUES(1,'B1'),(1,'B2'),(2,'B1'),(3,'B1'),(3,'B2'),(3,'B3') CREATE TABLE #C(ID INT, Wine VARCHAR(10)) INSERT INTO #C(ID, Wine) VALUES(1,'C1'),(1,'C2'),(1,'C3'),(2,'C1'),(2,'C2'),(2,'C3'),(3,'C1'),(3,'C3') SELECT a.id,a.name,b.party, c.wine FROM #A a JOIN #B b ON a.id= b.id JOIN #C c ON a.id= c.id order BY 1,2,3,4 DROP TABLE #A DROP TABLE #B DROP TABLE #C now company want process the data remove duplicate from each data set and have output just like: ![alt text][2] party and wine need show unique one for name, no matter for order. Could some expart help me for this case? Thanks! [1]: /storage/temp/3843-clientdata.png [2]: /storage/temp/3842-final.png
sqljoinduplicate
final.png (25.9 KiB)
clientdata.png (19.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DazyParker avatar image
DazyParker answered
Hi, You can check this answer: http://stackoverflow.com/questions/7084577/removing-duplicates-from-sql-join I hope it will work for you. Good Luck!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you. it looks not work for this case. Group by did something like DISTINCT.
0 Likes 0 ·
I need professional help for this special case.
0 Likes 0 ·
DaSiHu avatar image
DaSiHu answered
Hi, The rows in the first result are distinct, but it seems you want to have only one observation of something... The example output you gave doesn't help clarify. The windowing functions (ROW_NUMBER() etc) are probably most helpful, as they allow you to partition the data and then select the records you want. Review and adapt? https://ask.sqlservercentral.com/questions/110949/query-for-ignore-duplicated-rows.html
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

you are right, for A1, party only have B1,B2, Wine will have C1,C2 and C3. .For A2,A3 are same as this. windowing function can help but still did not get distinct party, and wine for Name. I really need help for it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.