Help with this query (duplicate records)

• xRafo

  SSChasing Mays

  Points: 623

  More actions

  September 4, 2013 at 7:45 am

  #282756

  Hi all, i have this data:

  DECLARE @data TABLE

  (tesplan_id int,

  tesplan_name varchar(250)

  ,tc_external_id int

  ,tcversions_id int

  ,tc_name varchar(250)

  ,estimated_time int

  ,is_numeric int

  ,ticket int

  ,name varchar(150)

  ,prefix varchar(50)

  ,login varchar(50)

  ,Status char(1)

  ,EXECUTION_TS datetime)

  INSERT @data

  SELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','p','2013-08-23 10:38:54.000' UNION ALL

  SELECT 115905,'CLARK_5.2.0.0_Testing_Plan',3010,108838,'TC_IM6909_Verify a skid can be associated to a WIP skid',70,1,108836,'IM6909','Clark-','christian.jurupe','f','2013-08-22 16:54:43.000'

  select * from @data

  I want to stay with the last record executed (EXECUTION_TS), y mean with the '2013-08-23 10:38:54.000'.

  Thanks for the help!.

  ____________________________________________________________________________
  Rafo*

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  September 4, 2013 at 8:43 am

  #1647342

  CTE's can really make this kind of query so easy!

  Try this out:

  ;with dups as

  (

  Select tesplan_id, tc_external_id, tcversions_id, execution_ts,

  ROW_NUMBER() over (partition by tesplan_id, tc_external_id, tcversions_id

  order by execution_ts desc) as RowNum

  from @data

  )

  DELETE d1

  FROM dups d1

  WHERE d1.RowNum > 1

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• MyDoggieJessie

  SSC-Forever

  Points: 44519

  More actions

  September 4, 2013 at 10:16 am

  #1647398

  Probably more preferred to just select from the CTE where the RowNum = 1?SELECT * FROM dups d1

  WHERE d1.RowNum = 1

  ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience