Remove duplicate rows

• Sagar Sawant

  SSC Eights!

  Points: 853

  More actions

  March 8, 2012 at 2:35 am

  #252060

  Hi,

  Table "tbl_Duplicates" contains multiple number of rows.

  pk_ResId i_Number ReservationId

  1 19075 1677

  2 19078 1677

  3 19067 1678

  4 19078 1679

  5 19078 1681

  6 19081 1683

  7 19068 1678

  Now, I have to remove only those records which "i_Number" field record matches with the any coming row. So mine result should be as follow:

  pk_ResId tbl_Number ReservationId

  1 19075 1677

  2 19078 1677

  3 19067 1678

  6 19081 1683

  7 19068 1678

  Any help would be greatly appreciated.

  Thanks in advance.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 8, 2012 at 3:43 am

  #1456422

  That is how you should present this sort of question:

  I have a Table as per:

  create table #tbl_Duplicates

  (pk_ResId int not null,

  i_Number int,

  ReservationId int)

  -- sample data

  insert #tbl_Duplicates select 1, 19075, 1677

  insert #tbl_Duplicates select 2, 19078, 1677

  insert #tbl_Duplicates select 3, 19067, 1678

  insert #tbl_Duplicates select 4, 19078, 1679

  insert #tbl_Duplicates select 5, 19078, 1681

  insert #tbl_Duplicates select 6, 19081, 1683

  insert #tbl_Duplicates select 7, 19068, 1678

  I would like to have the following results:

  ....

  So far I've tried to do:

  ...

  =======================================

  If you do the above, answer will follow almost straight away:

  delete d1

  from #tbl_Duplicates d1

  join #tbl_Duplicates d2

  on d2.i_Number = d1.i_Number and d2.pk_ResId < d1.pk_ResId

  select * from #tbl_Duplicates

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  March 8, 2012 at 4:16 am

  #1456434

  Here's another way

  WITH CTE AS (

  SELECT *,

  ROW_NUMBER() OVER(PARTITION BY i_Number ORDER BY pk_ResId) AS rn

  FROM #tbl_Duplicates)

  DELETE FROM CTE

  WHERE rn>1;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 8, 2012 at 4:19 am

  #1456438

  Mark-101232 (3/8/2012)

  ---

  Here's another way

  WITH CTE AS (

  SELECT *,

  ROW_NUMBER() OVER(PARTITION BY i_Number ORDER BY pk_ResId) AS rn

  FROM #tbl_Duplicates)

  DELETE FROM CTE

  WHERE rn>1;

  And it's a better way as well! My method would have to scan table twice...

  Too much drinks Yesterday night....

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]