CODEWITHSUNDEEP
sqlmysqldatabase
Nullpoet
Nullpoet

Reputation: 11269

In mysql how do I find rows repeating with respect to all fields?

+------------+------------+
| student    | department |
+------------+------------+
| 1234567890 | CS         | 
| 1234567890 | ME         | 
| 1234567890 | CS         | 
| 000000001  | ME         | 
+------------+------------+

How can I get rows that are repeating with respect to both fields?
Thanks in advance.

Upvotes: 0

Views: 92

Answers (3)

Sampson
Sampson

Reputation: 268424

SELECT student, department, count(*) as 'count' 
FROM students
GROUP BY student, department
HAVING count > 1

+------------+------------+-------+
| student    | department | count |
+------------+------------+-------+
| 1234567890 | CS         | 2     |
+------------+------------+-------+

Upvotes: 3

Anax
Anax

Reputation: 9372

SELECT * 
FROM mytable
GROUP BY student, department
HAVING COUNT( * ) > 1;

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166486

It should be something like

SELECT  student,
    department      
FROM    Table
GROUP BY student, department
HAVING COUNT(*) > 1

Upvotes: 4

Related Questions