How to select rows with same Id but different value in SQL Server
By FoxLearn 5/15/2024 4:02:02 AM 216
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
- How to export data to .csv file
- How to Read Excel file and Import data from Excel to SQL Server in C#
- How to Create a backup SQL Server in C#
- How to Search data from Database in C#
- How to rename a column name in SQL
- How to add primary key to existing table in SQL Server
- How to add index in SQL Server
- How to Download and Restore Northwind database to SQL Server