March 6, 2014 at 4:31 am
Purpose : I would like to get only one row for the duplicated based on specific column.
I want to get the row with minimum ID
Example :
ID , Code , StudentName , Age , Term , NumberOfSubjects
1 , 1000 , Ahmed , 10 , First , 5
2 , 1001 , Mohamed , 14 , Second , 6
3 , 1002 , Mahmoud , 12, First, 5
4 , 1001 , Mohamed , 14 , First , 4
5 , 1003 , Ali , 15 , Third , 2
6 , 1000 , Ahmed , 10 , Second , 3
7 , 1001 , Mohamed , 14 , Third , 7
I would like to get result like that :
1 , 1000 , Ahmed , 10 , First , 5
2 , 1001 , Mohamed , 14 , Second , 6
3 , 1002 , Mahmoud , 12, First, 5
5 , 1003 , Ali , 15 , Third , 2
Explain : The above example I have 2 records for user with code 1000 & 1002 and 3 records for user with code 1001
I want to get only the first record based on ID Order for the duplicated records based on Code Column as Grouping
So any suggestion about how to handle that ??
Notes : I know that table needs to be normalized to avoid that duplication but for some reasons I will not be able to normalize it. (Splitting Term & NumberOfSubject into new table not applicable in my situation)
March 6, 2014 at 4:35 am
SELECT ID, Code, StudentName, Age, Term, NumberOfSubject
FROM
(
SELECT ID, Code, StudentName, Age, Term, NumberOfSubject, RID = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY ID)
FROM myTable
) tmp
WHERE RID = 1;
It would be easier to write and test queries for your question if you would provide table DDL and sample data.
Please see the first link in my signature on how to do that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply