Forum Stats

  • 3,872,912 Users
  • 2,266,489 Discussions
  • 7,911,381 Comments

Discussions

Order by spend and followed by year

Siva  ManU
Siva ManU Member Posts: 16 Red Ribbon

i have 5 years spend data for 3 products like below , same can find in live sql also .


REPORT_ID ENTITY_ID TMC FY

458 9106166 7522 2025

458 9106166 7303 2024

458 9106166 7125 2023

458 9106166 6951 2022

458 9106166 6951 2021

458 8764180 6813 2025

458 8764180 6573 2024

458 8764180 6386 2023

458 8764180 5910 2022

458 8764180 5684 2021

458 0594502 7257 2025

458 0594502 7041 2024

458 0594502 6832 2023

458 0594502 6800 2022

458 0594502 6769 2021


can you please help me to get below result using ORDER by clause .

1) first pick 2021 data , find max tmc

2)based on 2021 tmc ,remaining years( entity_id ) data should follow.

Output should be like below

REPORT_ID ENTITY_ID TMC FY

458 9106166 6951 2021

458 9106166 6951 2022

458 9106166 7125 2023

458 9106166 7303 2024

458 9106166 7522 2025

458 0594502 6769 2021

458 0594502 6800 2022

458 0594502 6832 2023

458 0594502 7041 2024

458 0594502 7257 2025

458 8764180 5684 2021

458 8764180 5910 2022

458 8764180 6386 2023

458 8764180 6573 2024

458 8764180 6813 2025

Tagged:

Best Answer

  • Stax
    Stax Member Posts: 184 Silver Badge
    Answer ✓
    with t (REPORT_ID,ENTITY_ID,TMC,FY) as (
    select 458, '9106166', 7522, 2025 from dual union all
    select 458, '9106166', 7303, 2024 from dual union all
    select 458, '9106166', 7125, 2023 from dual union all
    select 458, '9106166', 6951, 2022 from dual union all
    select 458, '9106166', 6951, 2021 from dual union all
    select 458, '9106166', 6960, 2020 from dual union all
    select 458, '8764180', 6813, 2025 from dual union all
    select 458, '8764180', 6573, 2024 from dual union all
    select 458, '8764180', 6386, 2023 from dual union all
    select 458, '8764180', 5910, 2022 from dual union all
    select 458, '8764180', 5684, 2021 from dual union all
    select 458, '0594502', 7257, 2025 from dual union all
    select 458, '0594502', 7041, 2024 from dual union all
    select 458, '0594502', 6832, 2023 from dual union all
    select 458, '0594502', 6800, 2022 from dual union all
    select 458, '0594502', 6769, 2021 from dual )
    select t.* 
    from t
    order by 
       REPORT_ID
      ,max(decode(FY,2021,tmc)) over (partition by REPORT_ID,ENTITY_ID)
      ,FY
    /
    SQL> /
    
    
     REPORT_ID ENTITY_        TMC         FY
    ---------- ------- ---------- ----------
           458 8764180       5684       2021
           458 8764180       5910       2022
           458 8764180       6386       2023
           458 8764180       6573       2024
           458 8764180       6813       2025
           458 0594502       6769       2021
           458 0594502       6800       2022
           458 0594502       6832       2023
           458 0594502       7041       2024
           458 0594502       7257       2025
           458 9106166       6960       2020
           458 9106166       6951       2021
           458 9106166       6951       2022
           458 9106166       7125       2023
           458 9106166       7303       2024
           458 9106166       7522       2025
    
    
    16 rows selected.
    
    
    
    16 rows selected.
    
    

Answers

  • Stax
    Stax Member Posts: 184 Silver Badge
    Answer ✓
    with t (REPORT_ID,ENTITY_ID,TMC,FY) as (
    select 458, '9106166', 7522, 2025 from dual union all
    select 458, '9106166', 7303, 2024 from dual union all
    select 458, '9106166', 7125, 2023 from dual union all
    select 458, '9106166', 6951, 2022 from dual union all
    select 458, '9106166', 6951, 2021 from dual union all
    select 458, '9106166', 6960, 2020 from dual union all
    select 458, '8764180', 6813, 2025 from dual union all
    select 458, '8764180', 6573, 2024 from dual union all
    select 458, '8764180', 6386, 2023 from dual union all
    select 458, '8764180', 5910, 2022 from dual union all
    select 458, '8764180', 5684, 2021 from dual union all
    select 458, '0594502', 7257, 2025 from dual union all
    select 458, '0594502', 7041, 2024 from dual union all
    select 458, '0594502', 6832, 2023 from dual union all
    select 458, '0594502', 6800, 2022 from dual union all
    select 458, '0594502', 6769, 2021 from dual )
    select t.* 
    from t
    order by 
       REPORT_ID
      ,max(decode(FY,2021,tmc)) over (partition by REPORT_ID,ENTITY_ID)
      ,FY
    /
    SQL> /
    
    
     REPORT_ID ENTITY_        TMC         FY
    ---------- ------- ---------- ----------
           458 8764180       5684       2021
           458 8764180       5910       2022
           458 8764180       6386       2023
           458 8764180       6573       2024
           458 8764180       6813       2025
           458 0594502       6769       2021
           458 0594502       6800       2022
           458 0594502       6832       2023
           458 0594502       7041       2024
           458 0594502       7257       2025
           458 9106166       6960       2020
           458 9106166       6951       2021
           458 9106166       6951       2022
           458 9106166       7125       2023
           458 9106166       7303       2024
           458 9106166       7522       2025
    
    
    16 rows selected.
    
    
    
    16 rows selected.
    
    
  • Siva  ManU
    Siva ManU Member Posts: 16 Red Ribbon

    Thanks Stax,

    you tried with 2020 data also