newkey | code | name | Tvalue |
AAA | E | 0 | 20151203 |
AAA | N | 0 | 20151203 |
AAA | E | 0 | 20151204 |
AAA | D | 0 | 20151204 |
AAA | E | 0 | 20151205 |
AAA | N | 0 | 20151205 |
BBB | E | 0 | 20151203 |
TableA라는 곳에 위와 같은 자료가 저장되어 있다고 가정할때
제가 남기고자 하는 값은 아래와 같습니다.
newkey | code | name | Tvalue |
AAA | E | 0 | 20151205 |
AAA | N | 0 | 20151205 |
BBB | E | 0 | 20151203 |
만 남기고 나머지값들은 제거를 해줘야 합니다.
어떻게 해야 할지...처리방법이 있는지요?
도움 부탁드립니다
Comment 2
-
Terry
2015.12.03 15:33
-
minsouk
2015.12.04 09:58
exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....exitst.....exists.....use tempdb
goif object_id ('tblx') is not null
drop table tblx
go
create table tblx
(newkey varchar(100)
,code varchar(100)
,name varchar(100)
,Tvalue varchar(100)
)
go
insert into tblx
Select 'AAA', 'E', 0, '20151203' Union All
Select 'AAA', 'N', 0, '20151203' Union All
Select 'AAA', 'E', 0, '20151204' Union All
Select 'AAA', 'D', 0, '20151204' Union All
Select 'AAA', 'E', 0, '20151205' Union All
Select 'AAA', 'N', 0, '20151205' Union All
Select 'BBB', 'E', 0, '20151203'select * from tblx
godelete t
from
(
select row_number() over (partition by newkey, code, name order by Tvalue desc) rn
from tblx
) t
where rn > 1
goselect * from tblx
go
newkey, code ,name 3가지를 Grouping 하여 Tvalue 값 기준으로
최고값만 남기는거죠?
with tblA(newkey, code , name , Tvalue) As
(
Select 'AAA', 'E', 0, '20151203' Union All
Select 'AAA', 'N', 0, '20151203' Union All
Select 'AAA', 'E', 0, '20151204' Union All
Select 'AAA', 'D', 0, '20151204' Union All
Select 'AAA', 'E', 0, '20151205' Union All
Select 'AAA', 'N', 0, '20151205' Union All
Select 'BBB', 'E', 0, '20151203'
)
--Delete
Select *
From tblA
Where Exists (
Select 'x'
From
(
Select a.*
,Row_Number() Over(Partition By a.newkey,a.code,a.name Order By Tvalue Desc ) As seq
From tblA a
) a
Where a.seq <> 1
And tblA.newkey = a.newkey
And tblA.code = a.code
And tblA.name = a.name
And tblA.Tvalue = a.Tvalue
)