How to retrieve data from previous record from maximum in ORACLE DB-CodePudding

I have a table with Client Name, Contract_id, and four columns Total1, Total2, Total3 and Total4.

I need to retrieve in one row for each client the values of Total1, Total2, Total3 and Total4 from maximum record and previous record.

Have next:

enter image description here

Need to get like that:

enter image description here

Thank you!

CodePudding user response：

You can use something like the below which comes close to your requirement.The DB Fiddle here

with data as (select t.*,Row_number() over(partition by c_name order by rownum) row_num from testtt1 t 

    ),data1 as (
    select c_name,LISTAGG((c_id || ',' || t1 ||','|| t2 || ',' || t3 ||',' ||t4), '| ') WITHIN GROUP (ORDER BY row_num)  product_info from data
    where row_num<=2
    group by c_name
    union 
    select 'c_name' c_name,LISTAGG(('c_id' || ',' || 't1' ||','|| 't2' || ',' || 't3' ||',' ||'t4'), '| ') WITHIN GROUP (ORDER BY row_num)  product_info from data
    where row_num<=1
    group by 'c_name'
    )
    select c_name as " ",replace(product_info,',',chr(9)) as " " from data1
    order by decode(c_name,'c_name',c_name) ;

The Answer looks like the below

        c_name  c_id    t1  t2  t3  t4| c_id    t1  t2  t3  t4
        Company 583002  100 200 300 400| 564015 100 800 500 400
        Ontario 550705  100 200 300 400| 552854 100 800 500 400

CodePudding user response：

You can use LAST_VALUE analytic function with windowing clause looking just current and next row. I preserved the order of your rows from question by creating "tblord" CTE and then do the rest. You can skip this part if there is another way to sort your data. In that case just put your sorting instead of mine ORDER BY ROWNO everywhere it appears. The final row of a client is excluded as it has the same CONTRACT_ID and NEXT_CONTRACT_ID as well as all the other columns. If you want them too then just remove the outer query and it's WHERE clause. Regards...

WITH
    tbl AS
        (
          Select 'Ontario Inc.' "CLIENT_NAME", '550705' "CONTRACT_ID", 5667.9 "TOTAL1", 325.1 "TOTAL2", 677.8 "TOTAL3", 78890.78 "TOTAL4" From Dual UNION ALL
          Select 'Ontario Inc.' "CLIENT_NAME", '552854' "CONTRACT_ID", 6779.98 "TOTAL1", 223.5 "TOTAL2", 811.9 "TOTAL3", 10000.01 "TOTAL4" From Dual UNION ALL
          Select 'Ontario Inc.' "CLIENT_NAME", '564015' "CONTRACT_ID", 2234 "TOTAL1", 345.9 "TOTAL2", 799.67 "TOTAL3", 1100.09 "TOTAL4" From Dual UNION ALL
          Select 'Ontario Inc.' "CLIENT_NAME", '583002' "CONTRACT_ID", 657 "TOTAL1", 245 "TOTAL2", 888 "TOTAL3", 1000 "TOTAL4" From Dual UNION ALL
          Select 'Company Inc.' "CLIENT_NAME", '564015' "CONTRACT_ID", 2234 "TOTAL1", 345.9 "TOTAL2", 799.67 "TOTAL3", 1100.09 "TOTAL4" From Dual UNION ALL
          Select 'Company Inc.' "CLIENT_NAME", '583002' "CONTRACT_ID", 657 "TOTAL1", 245 "TOTAL2", 888 "TOTAL3", 1000 "TOTAL4" From Dual UNION ALL
          Select 'Company Inc.' "CLIENT_NAME", '550705' "CONTRACT_ID", 5667.9 "TOTAL1", 325.1 "TOTAL2", 677.8 "TOTAL3", 78890.78 "TOTAL4" From Dual UNION ALL
          Select 'Company Inc.' "CLIENT_NAME", '552854' "CONTRACT_ID", 6779.98 "TOTAL1", 223.5 "TOTAL2", 811.9 "TOTAL3", 10000.01 "TOTAL4" From Dual 
        ),
    tblord AS
        (
            SELECT
              ROW_NUMBER() OVER(ORDER BY 1) "ROWNO",
              CLIENT_NAME "CLIENT_NAME",
              CONTRACT_ID "CONTRACT_ID",
              TOTAL1 "TOTAL1",
              TOTAL2 "TOTAL2",
              TOTAL3 "TOTAL3",
              TOTAL4 "TOTAL4"
            FROM
              tbl
        )
SELECT
    *
FROM
    (
        SELECT
            CLIENT_NAME, CONTRACT_ID, TOTAL1, TOTAL2, TOTAL3, TOTAL4,
            LAST_VALUE(CONTRACT_ID) OVER(PARTITION BY CLIENT_NAME ORDER BY ROWNO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "NEXT_CONTRACT_ID",
            LAST_VALUE(TOTAL1) OVER(PARTITION BY CLIENT_NAME ORDER BY ROWNO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "NEXT_TOTAL1",
            LAST_VALUE(TOTAL2) OVER(PARTITION BY CLIENT_NAME ORDER BY ROWNO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "NEXT_TOTAL2",
            LAST_VALUE(TOTAL3) OVER(PARTITION BY CLIENT_NAME ORDER BY ROWNO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "NEXT_TOTAL3",
            LAST_VALUE(TOTAL4) OVER(PARTITION BY CLIENT_NAME ORDER BY ROWNO ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "NEXT_TOTAL4"
        FROM  
            tblord
        ORDER BY
            ROWNO
    )
WHERE 
    CONTRACT_ID <> NEXT_CONTRACT_ID
--
--  R e s u l t
--  
--  CLIENT_NAME  CONTRACT_ID     TOTAL1     TOTAL2     TOTAL3     TOTAL4 NEXT_CONTRACT_ID NEXT_TOTAL1 NEXT_TOTAL2 NEXT_TOTAL3 NEXT_TOTAL4
--  ------------ ----------- ---------- ---------- ---------- ---------- ---------------- ----------- ----------- ----------- -----------
--  Ontario Inc. 550705          5667.9      325.1      677.8   78890.78 552854               6779.98       223.5       811.9    10000.01 
--  Ontario Inc. 552854         6779.98      223.5      811.9   10000.01 564015                  2234       345.9      799.67     1100.09 
--  Ontario Inc. 564015            2234      345.9     799.67    1100.09 583002                   657         245         888        1000 
--  Company Inc. 564015            2234      345.9     799.67    1100.09 583002                   657         245         888        1000 
--  Company Inc. 583002             657        245        888       1000 550705                5667.9       325.1       677.8    78890.78 
--  Company Inc. 550705          5667.9      325.1      677.8   78890.78 552854               6779.98       223.5       811.9    10000.01