-1

I have two tables..

1-Identity(id, ref1,ref2, address)
2-details(ref1,ref2,amount,u_no,u_date)

I want to extract the each id with sum of amount having highest u_date and highest u_no

i tried below--

Select I.id, d.amount
From identity I Inner Join
     (select ref1,ref2,sum(amount) as amount
      From details d
      where (ref1,ref2,u_no,u_date) In (select ref1, ref2, max(u_no) as u_no, max(u_date) as u_date from details group By ref1,ref2)
Group By ref1,ref2)
     ) d
     On I.ref1 = d.ref1 And I.ref2 = d.ref2;

But I am getting same id with multiple amount. Table details and expected output

Can someone plz help me with this.thanks

Django
  • 11
  • 3

2 Answers2

0

Is this what you want?

select d.*
from (select d.*,
             row_number() over (partition by ref1, ref2 order by u_date desc, u_no desc) as seqnum
      from details d
     ) d
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i have attached the expected output image in description..could u plz take a took.i am getting bit confused with sum(amount) field..many thanks – Django Mar 22 '18 at 02:59
0

The desired result can be accomplished simple as this:

Select id,max(amount) AS amount
From identity
Join ref
using (Ref1,Ref2)
Group by id

If u need more columns in the result but still only one per id, alter the desired input output to reflect that, and we can help with that, but it will most likely involve the row_number() trick Gordon showed you

Lars G Olsen
  • 1,093
  • 8
  • 11