CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Raghu kanala
Raghu kanala

Reputation: 109

Finding Max value from result set

I am finding the max value from result set and have to skip the latest month max value. How can i do this?

Result set data :

1015|101|RT|2017/10|173124
1015|101|RT|2017/11|171053
1015|101|RT|2017/12|176462
1015|101|RT|2018/01|171064
1015|101|RT|2018/02|165132
1015|101|RT|2018/03|175738
1015|101|RT|2018/04|175072
1015|101|RT|2018/05|176743
1015|101|RT|2018/06|176541
1015|101|RT|2018/07|179983
1015|101|RT|2018/08|180841
1015|101|RT|2018/09|171178
1015|101|RT|2018/10|184095

Last column is the count value from result set. I am trying using Partition by and getting latest month data also.

select 
    client_cd,
    rep_no,
    prd_nm,
    date_ym,
    sum_cnt,
    max(sum_cnt) over(partition by PRD_NM,rep_no) as maxvalue,
    min(sum_cnt) over(partition by PRD_NM,rep_no) as minvalue
from res

Then I am getting Latest month value as Max value and Min Value. 

1015|101|RT|2017/10|173124|184095|165132
1015|101|RT|2017/11|171053|184095|165132
1015|101|RT|2017/12|176462|184095|165132
1015|101|RT|2018/01|171064|184095|165132
1015|101|RT|2018/02|165132|184095|165132
1015|101|RT|2018/03|175738|184095|165132
1015|101|RT|2018/04|175072|184095|165132
1015|101|RT|2018/05|176743|184095|165132
1015|101|RT|2018/06|176541|184095|165132
1015|101|RT|2018/07|179983|184095|165132
1015|101|RT|2018/08|180841|184095|165132
1015|101|RT|2018/09|171178|184095|165132
1015|101|RT|2018/10|184095|184095|165132

My result should be :

1015|101|RT|2017/10|173124|180841
1015|101|RT|2017/11|171053|180841
1015|101|RT|2017/12|176462|180841
1015|101|RT|2018/01|171064|180841
1015|101|RT|2018/02|165132|180841
1015|101|RT|2018/03|175738|180841
1015|101|RT|2018/04|175072|180841
1015|101|RT|2018/05|176743|180841
1015|101|RT|2018/06|176541|180841
1015|101|RT|2018/07|179983|180841
1015|101|RT|2018/08|180841|180841
1015|101|RT|2018/09|171178|180841
1015|101|RT|2018/10|184095|180841

Another set of data used for validaton but getting different result. I have used @D-shis Query.

1015|102|Addon|2017/10|13|20
1015|102|Addon|2017/11|13|20
1015|102|Addon|2017/12|19|20
1015|102|Addon|2018/01|15|20
1015|102|Addon|2018/02|21|20
1015|102|Addon|2018/03|17|20
1015|102|Addon|2018/04|14|20
1015|102|Addon|2018/05|20|20
1015|102|Addon|2018/06|12|20
1015|102|Addon|2018/07|19|20
1015|102|Addon|2018/08|18|20
1015|102|Addon|2018/09|9|20
1015|102|Addon|2018/10|14|20

I am getting above result after executing the query.

Expected output is..

1015|102|Addon|2017/10|13|21
1015|102|Addon|2017/11|13|21
1015|102|Addon|2017/12|19|21
1015|102|Addon|2018/01|15|21
1015|102|Addon|2018/02|21|21
1015|102|Addon|2018/03|17|21
1015|102|Addon|2018/04|14|21
1015|102|Addon|2018/05|20|21
1015|102|Addon|2018/06|12|21
1015|102|Addon|2018/07|19|21
1015|102|Addon|2018/08|18|21
1015|102|Addon|2018/09|9|21
1015|102|Addon|2018/10|14|21

Please help me how to do this?

Many thanks.

Upvotes: 0

Views: 148

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You can do this with conditional logic:

select r.*,
       (case when max(sum_cnt) keep (dense_rank first order by date_ym desc) over (partition by prd_num, rep_no) =
                  max(sum_cnt) over (partition by prd_nm, rep_no)
             else nth_value(sum_cnt, 2) over (partition by prd_nm, rep_no 
                                              order by sum_cnt desc
                                              rows between unbounded preceding and unbounded following
                                             )
         end)
from res r;

Upvotes: 1

GMB
GMB

Reputation: 222472

You can use an aggregate subquery to compute the maxvalue of each rep_no/prd_nm couple without including the latest value, and then JOIN it with your table :

SELECT 
    r.client_cd,
    r.rep_no,
    r.prd_nm,
    r.date_ym,
    r.sum_cnt,
    q.maxvalue,
    q.minvalue
FROM 
    res r
    INNER JOIN (
        SELECT rep_no, prd_nm, MAX(sum_cnt) maxvalue, MIN(sum_cnt) minvalue
        FROM res
        GROUP BY repo_no, prd_nm
        HAVING date_ym != MAX(date_ym)
    ) q ON q.rep_no = r.rep_no and q.prd_nm = r.prd_nm

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use DENSE_RANK window function with condition aggregate function to exclude max value.

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 minvalue
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

c# online

Upvotes: 1

Related Questions