How can I get only one row for the duplicated rows based on specific column ??

• ahmedhussein874

  SSC-Addicted

  Points: 497

  More actions

  March 6, 2014 at 4:31 am

  #284302

  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)

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  March 6, 2014 at 4:35 am

  #1694615

  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