안녕하세요.
다음과 같은 쿼리로 결과를 얻었을때
SELECT atch_file_id , atch_group_id , corp_cd , reg_dy , reg_ts , reg_user_id FROM tb_comm_atchfl01m a WHERE a.atch_file_sts_cd = 'I' AND a.corp_cd = 'ENDU' AND a.reg_dy = '20240215' AND a.reg_user_id = 'TX9RLK65' ORDER BY reg_ts DESC;
atch_file_id | atch_group_id | corp_cd | reg_dy | reg_ts | reg_user_id |
8LBUA1PR2W4 | M689H52EXD | ENDU | 20240215 | 2024-02-15 11:58:23.974 | TX9RLK65 |
JFC0K7GNYDQ | M689H52EXD | ENDU | 20240215 | 2024-02-15 11:58:23.962 | TX9RLK65 |
AEE12DFVEDR | 91N7AEFPB4 | ENDU | 20240215 | 2024-02-15 11:21:38.044 | TX9RLK65 |
FYTDFGW3DF | Q73M8EFPG1 | ENDU | 20240215 | 2024-02-15 11:15:55.753 | TX9RLK65 |
KJUUHHTDDR | LKQTX97V1U | ENDU | 20240215 | 2024-02-15 11:11:57.642 | TX9RLK65 |
PEODKFISDFF | LKQTX97V1U | ENDU | 20240215 | 2024-02-15 11:11:57.628 | TX9RLK65 |
reg_ts가 제일 최신데이터 중 atch_group_id 같은 건만 조회 되게 하고 싶습니다.
atch_group_id가 같은 건수는 여러 건 일수 있습니다.
그래서 원하는 결과는 아래와 같습니다.
도움 부탁드립니다.
tch_file_id | atch_group_id | corp_cd | reg_dy | reg_ts | reg_user_id |
8LBUA1PR2W4 | M689H52EXD | ENDU | 20240215 | 2024-02-15 11:58:23.974 | TX9RLK65 |
JFC0K7GNYDQ | M689H52EXD | ENDU | 20240215 | 2024-02-15 11:58:23.962 | TX9RLK65 |
SELECT atch_file_id , atch_group_id , corp_cd , reg_dy , reg_ts , reg_user_id , FROM (SELECT atch_file_id , atch_group_id , corp_cd , reg_dy , reg_ts , reg_user_id , MAX(reg_ts) OVER() ts1 , MAX(reg_ts) OVER(PARTITION BY atch_group_id) ts2 FROM tb_comm_atchfl01m WHERE atch_file_sts_cd = 'I' AND corp_cd = 'ENDU' AND reg_dy = '20240215' AND reg_user_id = 'TX9RLK65' ) a WHERE ts1 = ts2 ORDER BY reg_ts DESC ;