ㄔ, Oracle - rextester

Language: Layout:

CREATE TABLE res(
    client_cd INT, 
    rep_no INT,
    prd_nm VARCHAR(5),
    date_ym VARCHAR(50),
    sum_cnt INT
)\\

INSERT INTO res VALUES (1015,101,'RT','2017/10',173124)\\
INSERT INTO res VALUES (1015,101,'RT','2017/11',171053)\\
INSERT INTO res VALUES (1015,101,'RT','2017/12',176462)\\
INSERT INTO res VALUES (1015,101,'RT','2018/01',171064)\\
INSERT INTO res VALUES (1015,101,'RT','2018/02',165132)\\
INSERT INTO res VALUES (1015,101,'RT','2018/03',175738)\\
INSERT INTO res VALUES (1015,101,'RT','2018/04',175072)\\
INSERT INTO res VALUES (1015,101,'RT','2018/05',176743)\\
INSERT INTO res VALUES (1015,101,'RT','2018/06',176541)\\
INSERT INTO res VALUES (1015,101,'RT','2018/07',179983)\\
INSERT INTO res VALUES (1015,101,'RT','2018/08',180841)\\
INSERT INTO res VALUES (1015,101,'RT','2018/09',171178)\\
INSERT INTO res VALUES (1015,101,'RT','2018/10',184095)\\

SELECT client_cd,
       rep_no,
       prd_nm,
       date_ym,
       sum_cnt,
       MAX(CASE WHEN rn > 1 THEN sum_cnt END) over(partition by PRD_NM,rep_no) as maxvalue,
       MIN(CASE WHEN rn > 1 THEN sum_cnt END) over(partition by PRD_NM,rep_no) as maxvalue
FROM (
    select client_cd,
           rep_no,
           prd_nm,
           date_ym,
           sum_cnt,
           DENSE_RANK () over(partition by PRD_NM,rep_no ORDER BY sum_cnt DESC) as rn 
    from res
) t1\\

DROP TABLE res\\


absolute service time: 0,94 sec