sql task of reporting

Question

I have a sql query but I would like to improve them.

customer number | amount |  bill |  sum1 | sum2 |  date  | .... (other components)

  1001             -130     F/001     0     -70    2016-01-01

  1001             -80      F/002    -10     0     2016-05-01 

I would like to have as show below.

the amount in the report is chosen as the most recent of the dates for the account, but with the components sum1 and sum2 in report

customer number | amount |   sum1 | sum2 |  date | .... (other components)

  1001             -80        0     -70    2016-05-01

  1001             -80       -10     0     2016-05-01 

This subquery is owned by the company, I can not show it.

amendment might look just like this

 SELECT 
      customer_number, 
      amount,
      sum1,
      sum2,
      date,
      (other components)
 FROM (
      (content of the report)
 ) AS raport 
 WHERE ....

I tried with:

 SELECT 
      customer_number, 
      amount,
      sum1,
      sum2,
      date, 
      (other components) 
 FROM (
      (content of the report)
 ) AS raport 
QUALIFY ROW NUMBER() OVER (PARTITION customer_number, amount ORDER BY date DESC) = 1 

but I failed to.

I would ask for some suggestions.

Answer 1

I don't quite understand your requirement, but perhaps even so this will help. In your original post you flipped the dates, but not the amounts shown in the SUM1 and SUM2 columns. That makes no sense, clearly you made a mistake but I don't know what you wanted there. Anyway, the query below uses the FIRST_VALUE() analytic function, that is probably what you are looking for. If you don't need the ORDER BY clause at the very end, just delete/ignore it.

with
     inputs ( customer_number, amount, bill, sum1, sum2, dt ) as (
       select 1001, -130, 'F/001',   0, -70, date '2016-01-01' from dual union all
       select 1001,  -80, 'F/002', -10,   0, date '2016-05-01' from dual
     )
select customer_number, 
       first_value(amount) over (partition by customer_number order by dt desc) as amount,
       bill, sum1, sum2, dt
from   inputs
order by dt desc
;

Output:

CUSTOMER_NUMBER     AMOUNT BILL        SUM1       SUM2 DT
--------------- ---------- ----- ---------- ---------- ----------
           1001        -80 F/002        -10          0 2016-05-01
           1001        -80 F/001          0        -70 2016-01-01

Answer 2

From the given information I think you are looking for an order by descending clause:

 SELECT 
  customer_number, 
  amount,
  sum1,
  sum2,
  date,
  (other components)
FROM (
  (content of the report)
 ) AS raport 
 Order By date desc