finding duplication in data

Question

I have data of students' result. Each student has appeared in 5 subjects. Few students have more than 5 records in database. Student having 2 records in "Science" and 1 for each remaining subjects. total 6 records. example is given here.

sample data:

RollNo          Subject   TOTAL_MARKS
-------------------------------------
11-119-302        ENGL      34
11-119-302        MATH      56
11-119-302        SCIE      44
11-119-302        SCIE      24
11-119-302        URDU      76

I want to get all the fields of those students having duplicate subjects.

Required output:

RollNo          Subject   TOTAL_MARKS
-------------------------------------    
11-119-302        ENGL      34
11-119-302        ENGL      56
11-119-333        SCIE      44
11-119-333        SCIE      24

possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Bud Damyanov, Aug 13 '14 at 08:54

score 1 · Answer 1 · answered Aug 13 '14 at 08:29

1

try this :

select * from results where
         num in ( select num from results group by num having count(num)>2)

answered Aug 13 '14 at 08:29

Youness

1,468
1
9
19

score 1 · Accepted Answer · answered Aug 13 '14 at 08:39

1

See this

DECLARE @MARKS TABLE (ROLLNO VARCHAR(20),SUBJECT VARCHAR(20), TOTAL_MARKS INT)
INSERT INTO @MARKS VALUES 
('11-119-302','ENGL',34),
('11-119-302','MATH',56),
('11-119-302','SCIE',44),
('11-119-302','SCIE',24),
('11-119-302','URDU',76)

SELECT  *
FROM    @MARKS M JOIN (
SELECT  ROLLNO,SUBJECT,COUNT(*) C FROM @MARKS GROUP BY ROLLNO,SUBJECT HAVING COUNT(*) > 1) LU
ON      M.ROLLNO = LU.ROLLNO AND M.SUBJECT = LU.SUBJECT

--Result

enter image description here

answered Aug 13 '14 at 08:39

Jithin Shaji

5,893
5
25
47

Jithin thanks a lot. your query worked exactly. but it is a little bit complicated to understand. Is it possible to have any alternate query? or a query without Join? – Shah Nawaz Aug 13 '14 at 10:05
Youness ur query selects all the records from table, whereas query suggested by Jithin produce correct output. can you simplify Jithin query? – Shah Nawaz Aug 13 '14 at 10:07
@ShahNawaz, are u asking, whether the SQL can be even simplified.? – Jithin Shaji Aug 13 '14 at 10:15
yes Jithin, is there any simplied sql query instead what you addred previously. – Shah Nawaz Aug 13 '14 at 10:35
I am not sure, May be someone will come up with a better answer. But the one specified is a simple one. It only does basic operations. – Jithin Shaji Aug 13 '14 at 10:37
Jithin Consider this, is it an alternate of your query? – Shah Nawaz Aug 13 '14 at 10:47
SELECT rollno, Subject, COUNT(*) FROM RRAttock GROUP BY RollNo, Subject HAVING COUNT(*) > 1 order by RollNo – Shah Nawaz Aug 13 '14 at 10:48
Only difference is, this query do not returns all fields of table – Shah Nawaz Aug 13 '14 at 10:50
You query will give like (rollno --> 11-119-302 Subject --> SCIE (No column name) --> 2). You wont get the result you asked in the question.Again it should be COUNT(*), not COUNT() – Jithin Shaji Aug 13 '14 at 10:54

finding duplication in data

2 Answers2