로

mssql 중복 데이터 질문인데요 부탁좀 드릴꼐요 ㅜㅜ 계속 삽질이네요

  select distinct(SVUSERID) 
   , Row_Number() over(order by CAST(SVDT AS float)) as RN
   , STFSEQ
   , PBLSH
   , (select CODNAME from FN_COD_MST where GRID='1011' and COD=PBLSH) PBLSHCODE   
   , STFNAME
   , ZIPCODE
   , ADRESS1 + ' ' + ADRESS2 as ADRESS
   , ADRESS2
   , TELNO1 + '-' + TELNO2 + '-' + TELNO3 as TELNO
   , PNAME
   , EMAIL
   , JSSEQ
   , CAST(SVDT AS float) SVDTi
     from FN_STF_MST
  where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
  order by SVDTi
 
위와 같은 쿼리 실행시 결과가
 
table.JPG?type=w620
 
위와 같이 나옵니다.   
 
off1 
off3
off1 
순서로 3개의 레코드가 나옵니다.
 
이때
 
off1
off3
 
2개의 레코드만 나오게하고 싶은데 부탁 드릴께요
DISTINCT, GROUP BY 다 해봤는데
 
첫번째 off1과 세번째 off1은 SVUSERID 빼고는 내용이 모두 달라서 안되는 군요
 
즉 다음 그림과 같이 나오게 하고 싶습니다.
table2.JPG?type=w620 

댓글 1개

자문자답이네요


select SVUSERID
, Row_Number() over(order by CAST(SVDT AS float)) as RN
, STFSEQ
, PBLSH
, (select CODNAME from FN_COD_MST where GRID='1011' and COD=PBLSH) PBLSHCODE
, STFNAME
, ZIPCODE
, ADRESS1 + ' ' + ADRESS2 as ADRESS
, ADRESS2
, TELNO1 + '-' + TELNO2 + '-' + TELNO3 as TELNO
, PNAME
, EMAIL
, JSSEQ
, SVDT
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
AND SVUSERID not in (
select SVUSERID
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
GROUP BY SVUSERID
having count(SVUSERID) > 1
)
OR STFSEQ in(
select top 1 STFSEQ
from FN_STF_MST
where SVUSERID =
( select SVUSERID
from FN_STF_MST
where PBLSH in (select COD from FN_COD_MST where GRID='1011' and CODNAME like '%' + '골드' + '%')
GROUP BY SVUSERID
having count(SVUSERID) > 1
) order by SVDT
)

다른 의견 있으신분 부탁드립니다.
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
11-10-25 조회 2,535
11-10-18 조회 4,093
11-10-18 조회 2,476
11-10-15 조회 3,673
11-10-10 조회 2,749
11-10-02 조회 2,640
11-09-28 조회 2,447
11-09-24 조회 2,538
11-09-22 조회 2,450
11-09-05 조회 2,925
11-08-31 조회 2,326
11-08-31 조회 2,633
11-08-31 조회 2,487
11-08-29 조회 3,025
11-08-28 조회 2,509
11-08-25 조회 2,569
11-08-17 조회 2,275
11-08-16 조회 2,519
11-08-11 조회 2,989
11-08-07 조회 2,691
11-08-03 조회 2,402
11-07-22 조회 2,141
11-07-12 조회 3,353
11-06-27 조회 2,121
11-06-01 조회 1,790
11-05-17 조회 2,595
11-05-01 조회 2,646
11-04-28 조회 2,942
11-04-28 조회 2,992
11-04-13 조회 2,660
11-03-27 조회 2,777
11-03-27 조회 2,662
11-03-14 조회 2,066
11-03-02 조회 2,392
11-03-02 조회 2,614
11-02-27 조회 3,041
11-02-23 조회 2,418
11-02-19 조회 2,565
11-02-14 조회 4,171
11-02-03 조회 2,955
11-01-24 조회 3,910
11-01-04 조회 2,441
10-12-08 조회 5,466
10-11-15 조회 2,466
10-11-14 조회 2,664
10-11-14 조회 3,678
10-10-29 조회 3,733
10-10-19 조회 2,750
10-10-07 조회 2,580
10-10-05 조회 4,503
10-09-18 조회 2,794
10-09-18 조회 2,540
10-09-17 조회 4,942
10-09-15 조회 2,879
10-09-15 조회 2,413
10-09-15 조회 2,902
10-09-15 조회 2,734
10-09-11 조회 4,064
10-09-11 조회 2,558
10-09-11 조회 2,611
10-09-07 조회 3,887
10-09-07 조회 3,421
10-08-29 조회 2,867
10-07-01 조회 3,133
10-06-30 조회 2,529
10-06-30 조회 2,713
10-06-30 조회 3,053
10-06-13 조회 2,111
10-04-15 조회 3,274
10-04-04 조회 3,509
10-02-23 조회 3,620
10-02-09 조회 3,191
10-01-25 조회 2,710
10-01-25 조회 2,809
09-12-27 조회 4,457
09-11-23 조회 3,679
09-11-20 조회 4,912
09-08-30 조회 3,117
09-08-30 조회 3,109
09-08-12 조회 3,083
09-07-22 조회 4,141
09-07-22 조회 4,783
09-07-15 조회 5,933
09-07-11 조회 3,015
09-06-17 조회 2,752
09-06-15 조회 3,048
09-05-29 조회 3,346
09-05-11 조회 5,967
09-05-08 조회 2,723
09-04-30 조회 3,848
09-04-17 조회 3,158
09-04-04 조회 3,352
09-03-05 조회 3,721
09-02-09 조회 3,702
09-02-03 조회 6,189
08-12-27 조회 3,253
08-12-20 조회 4,333
08-11-21 조회 5,292
08-11-14 조회 2,867
08-11-03 조회 3,342