Oracle SQL - Getting Max Date with several joins -


i think may overthinking this, i'm having issue when trying find max date several joins in oracle database several clauses. i've found many examples of simple max queries, nothing this. query works fine if add line in find records above specific date (there handful of results returned). however, want automatically recent date records bill table. database has additional table actual bill amount stored, adds layer.

select p.purchase_id, p.account_id, b.bill_date, bp.current_amount  purchases p join bill_purchases bp on p.purchase_id = bp.purchase_id join bills b on bp.bill_id = b.bill_id --need recent date bill table each bill p.type != 'cash' , bp.amount > '100.00'  , p.status = 'approved' , p.purchase_id in ( ... list of purchases ...); 

i have tried doing subqueries max functions in them, not having luck. each query returns same amount of records original query. how rearrange query still retrieve of necessary columns , clauses while still limiting recent purchase billed?

try below this

select p.purchase_id, p.account_id, b.bill_date, bp.current_amount  purchases p join bill_purchases bp on p.purchase_id = bp.purchase_id join (  select bill_id, max(bill_date) bill_date         bills          group bill_id      )b on bp.bill_id = b.bill_id p.type != 'cash' , bp.amount > '100.00'  , p.status = 'approved' , p.purchase_id in ( ... list of purchases ...); 

Comments

Popular posts from this blog

javascript - Slick Slider width recalculation -

jsf - PrimeFaces Datatable - What is f:facet actually doing? -

angular2 services - Angular 2 RC 4 Http post not firing -