sqloracle-database

Select minimum date value in a table

I have data in this format:

Account  security_name, broker_name balance_bf, tran_amt  tran_date

2323232  sec_a          br_a         400         -200      06-10-2014
2323232  sec_b          br_b         800         400       06-10-2014
2323232  sec_a          br_a         200         -300      06-10-2014
2323232  sec_a          br_a         200         -300      07-10-2014
2323232  sec_b          br_b         1200         400      09-10-2014

I need to write a result set such that the Opening balance_bf appears on every row based on security_name and the Broker name. For example.

account  security_name   broker_name  balance_bf  tran_amt   tran_date   first_tran_date
2323232  sec_a          br_a         400         -200      06-10-2014  06-10-2014
2323232  sec_b          br_b         800         400       06-10-2014  06-10-2014
2323232  sec_a          br_a         400         -300      06-10-2014  06-10-2014
2323232  sec_a          br_a         400         -300      07-10-2014  06-10-2014
2323232  sec_b          br_b         800          400      09-10-2014  06-10-2014

The Tran_date has a time stamp so I can order them I just want to get the first balance_bf and first tran_date on every row

I have tried

select account, security_name, broker_name,tran_amt, min(tran_date)first_tran_date
from customer_transactions group by account, security_name, broker_name,tran_amt order by tran_date;

Of course this does not work because the amount will be different.

Solution

  • You can use the FIRST_VALUE function:

    select Account, security_name, broker_name, balance_bf, tran_amt, tran_date,
   FIRST_VALUE(balance_bf) OVER (PARTITION BY security_name, broker_name ORDER BY tran_date) as first_balance,
   FIRST_VALUE(tran_date) OVER (PARTITION BY security_name, broker_name ORDER BY tran_date) as first_tran_date
from customer_transactions;