Select distinct combination of rows based on columns

Question

I have a table where the columns author1 and author2 have mirror data like (a,b) and (b,a). But I don't want the data to be redundant so a SQL query is needed for eliminating records that have (b,a) if (a,b) already exists.

author1              author2                No of Publications
Anna Spagnolli       Luciano Gamberini           115
Luciano Gamberini    Anna Spagnolli              115
Anna Spagnolli       Giuseppe Riva                66
Giuseppe Riva        Anna Spagnolli               66
Giuseppe Riva        Luciano Gamberini            60
Luciano Gamberini    Giuseppe Riva                60
Achim Jung           Anna Spagnolli               33
Anna Spagnolli       Achim Jung                   33
Anna Spagnolli       John A. Waterworth           33
John A. Waterworth   Anna Spagnolli               33

For eg, I don't want the second record here because it already exists in the first record

https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 — Arunkumar Muthuvel, Mar 29 '18 at 05:40
do you have any key in table ? if yes then add that column too in your sample data — Alpesh Jikadra, Mar 29 '18 at 06:03

score 2 · Accepted Answer · answered Mar 29 '18 at 06:21

2

Solution for your problem :

SELECT table1.author1, table1.author2, table1.No_Of_Publications FROM table1
LEFT JOIN table1 as tbl
ON table1.author1 = tbl.author2
AND table1.author2 = tbl.author1
WHERE table1.author1 < tbl.author1
OR tbl.author1 IS NULL

For demo:

http://sqlfiddle.com/#!9/2263fa/14

answered Mar 29 '18 at 06:21

Nishant Gupta

3,533
1
11
18

@Pumpkin Head Try my solution also. – Nishant Gupta Mar 29 '18 at 06:22

Gaurav · Answer 2 · 2018-03-29T05:59:28.300

1

Please try to use below query :-

select (case when author1> author2 then author2 else author1 end) as author1
      ,(case when author1> author2 then author1 else author2 end) as author2
      ,publications 
from calc
group by (case when author1> author2 then author2 else author1 end)
,(case when author1> author2 then author1 else author2 end)
,publications

edited Mar 29 '18 at 05:59

answered Mar 29 '18 at 05:45

Gaurav

1,070
9
11

Updated code, let me know if you still face challenge. – Gaurav Mar 29 '18 at 06:00

score 0 · Answer 3 · answered Mar 29 '18 at 06:01

Try this https://www.db-fiddle.com/f/buQqnt1YsfoWCuJoMdgVrz/0

CREATE TABLE table1 (
  author1 VARCHAR(100),
  author2 VARCHAR(100),
  publication INT
);

insert into table1 values ('a', 'b', 100);

insert into table1 values ('b', 'a', 100);

select   (case
  when t.author1>t.author2 then t.author1 else t.author2  END ) as a1 ,
   (case
  when t.author1>t.author2 then t.author2 else t.author1  END ) as a2 , publication

 from table1 as t group by a1, a2, publication;

score 0 · Answer 4 · edited Mar 29 '18 at 09:50

CREATE TABLE #TEMI1(AUTHOR1 CHAR(3),AUTHOR2 CHAR(3),NO_OF_PUB INT)

INSERT INTO #TEMI1 VALUES
('A','B',115),('B','A',115),('A','C',66),('C','A',66),('D','A',78),('B','C',115)

WITH CTE1 AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY NO_OF_PUB ORDER BY NO_OF_PUB) RNUM FROM #TEMI1 )

--SELECT * FROM CTE1 C1
--JOIN CTE1 C2
--ON C1.NO_OF_PUB=C2.NO_OF_PUB AND C1.AUTHOR1=C2.AUTHOR2 AND C1.AUTHOR2=C2.AUTHOR1


DELETE C1 FROM CTE1 C1 
JOIN CTE1 C2
ON C1.NO_OF_PUB=C2.NO_OF_PUB AND C1.AUTHOR1=C2.AUTHOR2 AND C1.AUTHOR2=C2.AUTHOR1
WHERE C1.RNUM>1

Select distinct combination of rows based on columns

4 Answers4