Search code examples
mysqlsqlmainframe

write a sql Query to fetch records with multiple values

I have a table with Roll Number, name. But for some of the roll numbers, there are records with different names

In the

enter image description here

table, Roll numbers 2 and 6 have multiple records with the same roll number but different names.

I need to write a query/cursor to fetch such records from the above table Student.

Eg: Table - Student

|Roll No.| Name |
| -------| -----|
| 1      | A    |
| 1      | A    |
| 2      | B    |
| 2      | C    |
| 2      | D    |
| 3      | E    |
| 3      | E    |
| 4      | F    |
| 5      | G    |
| 6      | H    |
| 6      | I    |

The expected output should be 2, 6(Roll numbers having multiple records but with different names)

Number 1, 3 should not in the expected result because they have same name in all the records

Solution

  • In MySQL you can do next query:

    select roll_no
from Student
group by roll_no
having count(distinct name) > 1;

    MySQL group by

    Result:

    +=========+
| roll_no |
+=========+
| 2       |
+---------+
| 6       |
+---------+