How to select rows with same Id but different value in SQL Server

I spent hours and read many articles to find a way to solve the above problem.

If you need to get a list of "duplicate" rows where the StudentId is the same value but they have a different value in a Number column.

Id   |   StudentId   |    Number
1    |   1054          |    111112
2    |   1054          |    111112
3    |   1054          |    111123
4    |   1061          |    232321
5    |   1061          |    232321

The query based on the above should return 1054, 1061

Id   |   StudentId   |  Number
1    |   1054          |    111112
3    |   1054          |    111123
4    |   1061          |    232321

How to select rows with same Id but different value in SQL Server

To select rows with the same Id but different values in another column in SQL Server, you can use a query with a subquery, or the PARTITION clauses.

Following is an idea how can i achieve this in SQL

select Id, StudentId, Number 
from ( select *,  ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY Id ASC) as n from tablename) x
where n = 1

You can create a partition column using StudentId which will repeat the row_number for every same value of Number column.

Finally, you need to eliminate duplicate records using where condition n = 1