CODEWITHSUNDEEP
oracle-database
AbraKaDabra
AbraKaDabra

Reputation: 3

select query in oracle

I have database design like this...

   dated       ref       weight  no.  address

21-03-2013  ABCD/EDFG     1234   A45   A1 
20-03-2013  ABCD/EDFG     789    A56   A2
25-03-2013  ABCD/EDFG     6981   A99   A5 
23-03-2013  GAJHS/ASDH     72    A82   GV

what i want here in the query result is something like this....
search on the basis of no.
but then, it has to see if the ref of that row exists more times and if it exists then it has to add the weight of all such rows keeping in mind dated of all such records should be less than the dated of selected no..

example - 

no. = A56
three rows exist with same ref(ABCD/EDFG)
but dated of A56 is lower among all so results should be 

     ref ------ weight --------  no. -------- address
ABCD/EDFG ------ 789 ----------- A56 -------- A2

but in the case of no. = A99 results should be like this - 

ref ----------- weight --------  no. -------- address
ABCD/EDFG --- (789+6981+1234) ----------- A99 -------- A5 

as dated of A99 is greater than other two records.

please help me in this query.

Upvotes: 0

Views: 56

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

select 
   ref, 
   sum(weight) over (partition by ref order by dated) as weight,
   no,
   address
from
   ...

Upvotes: 1

Related Questions