group by와 distinct 사용 시 order by 안되고 order by시 group by안되는 문제 시 파티션 사용!

1. group by와 order by로 가져왔다 

근데... rn땜시 이게 group by해도 중복제거가 안된다;; 

SELECT ORGANIZATION, RN FROM (
		 SELECT 
			   APTMT_YMD
			 , APTMT_CD
			 , ORGCD
			 , LAG(APTMT_YMD) OVER(ORDER BY APTMT_YMD)  AS APTMT_PREV
		     , LEAD(APTMT_YMD) OVER(ORDER BY APTMT_YMD) AS APTMT_NEXT
		 	 , HDQTS_ORGNM || ' ' || DEPT_ORGNM || ' ' || DEPTMT_ORGNM|| ' '|| TEAM_ORGNM AS ORGANIZATION
		 	 , ROW_NUMBER() OVER (ORDER BY APTMT_YMD DESC) AS RN
		 FROM TB_APTMT_MTTR 
		WHERE APTMT_CD NOT IN ('00D5', '00D9', '00B5', '00D2', '00D6', '00D0', '00D3', '00AC','00J2') -- 직위,직급,직렬 안 바뀌는 발령 코드
		AND empno = '115726'
		ORDER BY APTMT_YMD DESC
		)
		
		WHERE 
			(TO_CHAR(TRUNC(SYSDATE) - INTERVAL '5' YEAR, 'YYYYMMDD') BETWEEN APTMT_PREV AND APTMT_NEXT
			OR APTMT_YMD BETWEEN TO_CHAR(TRUNC(SYSDATE) - INTERVAL '5' YEAR, 'YYYYMMDD') AND TO_CHAR(TRUNC(SYSDATE)))
			
		GROUP BY ORGANIZATION, RN
		ORDER BY RN

 

 

그래서 rn을 없애고 group by하니깐.. 

 

중복제거는 되는데 순서가 뒤죽박죽임ㅋㅋ

 

 

---

해결방법 ---> partition 을 써서 값을 해당하는것 하나만 뽑아내자

 

여기서 rn = 1 추가 하면

 

 

SELECT ORGANIZATION, RN FROM (
		 SELECT 
			   APTMT_YMD
			 , APTMT_CD
			 , ORGCD
			 , LAG(APTMT_YMD) OVER(ORDER BY APTMT_YMD)  AS APTMT_PREV
		     , LEAD(APTMT_YMD) OVER(ORDER BY APTMT_YMD) AS APTMT_NEXT
		 	 , HDQTS_ORGNM || ' ' || DEPT_ORGNM || ' ' || DEPTMT_ORGNM|| ' '|| TEAM_ORGNM AS ORGANIZATION
		 	 , ROW_NUMBER() OVER (PARTITION BY HDQTS_ORGNM || ' ' || DEPT_ORGNM || ' ' || DEPTMT_ORGNM|| ' '|| TEAM_ORGNM ORDER BY APTMT_YMD DESC) AS RN
		 FROM TB_APTMT_MTTR 
		WHERE APTMT_CD NOT IN ('00D5', '00D9', '00B5', '00D2', '00D6', '00D0', '00D3', '00AC','00J2') -- 직위,직급,직렬 안 바뀌는 발령 코드
		AND empno = '115726'
		ORDER BY APTMT_YMD DESC
		)
		
		WHERE 
			(TO_CHAR(TRUNC(SYSDATE) - INTERVAL '5' YEAR, 'YYYYMMDD') BETWEEN APTMT_PREV AND APTMT_NEXT
			OR APTMT_YMD BETWEEN TO_CHAR(TRUNC(SYSDATE) - INTERVAL '5' YEAR, 'YYYYMMDD') AND TO_CHAR(TRUNC(SYSDATE)))
			AND RN = 1