September 4, 2013 at 7:45 am
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*
September 4, 2013 at 8:43 am
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/
September 4, 2013 at 10:16 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply