분석함수 row_number() 순서대로 RANK 반환

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

오류 시 유용하게 사용하는 분석함수 row_number()

ID 하나에 여러라인이 존재할 경우

최종변경일 기준으로 최신데이터 반환하도록 사용할때 많이 쓴다.

 

WITH test_table AS
 (SELECT 'A' t_id
,100 t_value
,SYSDATE - 1 t_date
  FROM   dual
  UNION ALL
  SELECT 'A' t_id
,200 t_value
,SYSDATE - 2 t_date
  FROM   dual
  UNION ALL
  SELECT 'B' t_id
,150 t_value
,SYSDATE - 3 t_date
  FROM   dual
  UNION ALL
  SELECT 'B' t_id
,250 t_value
,SYSDATE - 4 t_date
  FROM   dual)
SELECT * FROM test_table;

T_ID	T_VALUE	T_DATE
A	100	2022-09-15  1:45:01 PM
A	200	2022-09-14  1:45:01 PM
B	150	2022-09-13 오후 1:51:44
B	250	2022-09-12 오후 1:51:44

 

 

T_ID 별로 A : 100, B : 150을 반환하고 싶을 때(최신변경일 기준)

T_DATE로 DESC(내림차순) 하는 RANK를 구한다음 RANK가 1인 값을 반환하면 된다.

 

SELECT a.*
  ,row_number() over(PARTITION BY a.t_id ORDER BY a.t_date DESC) rn
FROM   test_table a;

T_ID	T_VALUE	T_DATE	RN
A	100	2022-09-15  1:45:01 PM	1
A	200	2022-09-14  1:45:01 PM	2
B	150	2022-09-13 오후 1:51:44	1
B	250	2022-09-12 오후 1:51:44	2

 

최종적으로

RN = 1 조건으로 조회를 하면

A : 100, B : 150 결과값을 가져올 수 있다.

 

WITH test_table AS
 (SELECT 'A' t_id
,100 t_value
,SYSDATE - 1 t_date
  FROM   dual
  UNION ALL
  SELECT 'A' t_id
,200 t_value
,SYSDATE - 2 t_date
  FROM   dual
  UNION ALL
  SELECT 'B' t_id
,150 t_value
,SYSDATE - 3 t_date
  FROM   dual
  UNION ALL
  SELECT 'B' t_id
,250 t_value
,SYSDATE - 4 t_date
  FROM   dual)
SELECT b.*
FROM   (SELECT a.*
  ,row_number() over(PARTITION BY a.t_id ORDER BY a.t_date DESC) rn
FROM   test_table a) b
WHERE  b.rn = 1

 

T_ID	T_VALUE	T_DATE	RN
A	100	2022-09-15  1:45:01 PM	1
B	150	2022-09-13 오후 1:51:44	1