0

I need to get the max record for each version in Oralce SQL 

Price  Total_Amount  Group   Version 
10      100           1       20190401.00
11      111           1       20190501.00 --- Print this version 
5       50            2       20190401.00 --- Print this version
6       60            3       20190401.00
7       70            3       20190501.00
8       80            3       20190601.00
9       90            3       20190701.00  --- Print this version

The above query was output from 

select
    sum(price),
    sum(Total_amount),
    group,
    version,
    row_number() over(Partition by group order by version) row_num,
    dense_Rank over ( order by version) dense_Rank
from Table_name 
group by group,version

I tried using Partition on group and dense rank in the above query it gives me max in each group but when i tried to query the result from above its not giving me the result i want

Desired output 

Price  Total_Amount  Group   Version
11      111           1       20190501.00 
5       50            2       20190401.00
9       90            3       20190701.00
MT0
  • 143,790
  • 11
  • 59
  • 117
A K
  • 25
  • 6
  • 1
    please define what you mean with "max record" – Sterconium Sep 11 '19 at 14:24
  • You want `order by version DESC` in the `row_number` analytic function and then in an outer query filter to `WHERE your_alias_for_the_row_number = 1`. (As per [this answer](https://stackoverflow.com/a/121693/1509264) in the linked duplicate.) – MT0 Sep 11 '19 at 14:28

1 Answers1

0

One method uses lead():

select t.*
from (select t.*,
             lead(version) over (partition by group order by version) as next_version
      from t
     ) t
group next_version is null;

In Oracle, you can also express this using aggregation, with the keep syntax:

select group, max(version),
       max(price) keep (dense_rank first order by version desc) as price,
       max(total_amount) keep (dense_rank first order by version desc) as total_amount
from t
group by group;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks for the quick response worked like charm will learn more about the Lead and Keep option never tried before appreciate your help Gordon Linoff – A K Sep 11 '19 at 14:43