How to select rows with same Id but different value in SQL Server
By FoxLearn 5/15/2024 4:02:02 AM 112
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 use Oracle linked server shows all tables
- SQL Server Migration Assistant
- How to Download and Restore Northwind database to SQL Server
- How to use SQL INSERT INTO SELECT Statement
- Data type SQL Server
- How to drop table if exists in sql server
- How to fix 'The specified directory for the INSTALLSHAREDDIR parameter is not valid'
- How to get all table names of a particular database using TSQL