CODEWITHSUNDEEP
sql
user3171906
user3171906

Reputation: 583

retrieving duplicate records through sql

I have a data like

paper Id Author Id  

1          15
2          13 
3          12
3          11 
4          10
4          10
4          9

I want to select papers having multiple authors.So my data should look like 

paper Id   Author Id
3            12
3            11
4            10
4             9

I tried with sql query :

statement<-"SELECT PaperId,AuthorId,COUNT(PaperId) 
            FROM author_data_pap_aut
            GROUP BY PaperId,AuthorId
            HAVING ( COUNT(*) >1 )"

But its not working.How can i get the desired result

Thanks

Upvotes: 0

Views: 76

Answers (2)

Vijay Barbhaya
Vijay Barbhaya

Reputation: 876

You need to use group_concat() function for do your process

Upvotes: 6

juergen d
juergen d

Reputation: 204746

SELECT PaperId 
FROM author_data_pap_aut
GROUP BY PaperId
HAVING COUNT(distinct AuthorId) > 1

And if you need the authors too then use 

select distinct PaperId, AuthorId
from author_data_pap_aut
where PaperId in
(
    SELECT PaperId 
    FROM author_data_pap_aut
    GROUP BY PaperId
    HAVING COUNT(distinct AuthorId) > 1
)

Upvotes: 1

Related Questions