sql - View population based on inter-row conditions -

i'm trying create , populate view there's tricky criteria that's slowing me lot. of data flowing in coming other tables in db.

i need pick row if row (of same table) comply given constrains: example, given record, i'll take if:

• it shares code record in same table;
• it has type;
• it's date matches 1 type same code;
• it's value bigger matching record;

examples:

1. code1 - type.a - 01/12 - 200 2. code1 - type.b - 01/12 - 300 3. code1 - type.b - 01/09 - 300 4. code1 - type.b - 01/12 - 100 5. code2 - type.b - 01/12 - 200 6. code1 - type.a - 01/12 - 300 

of records above, take row #2 because shares code row of type "a" (restrain 1), has type "b" (r2), has matching date said record (r3), , value bigger the matching record (300>200).

• row #3 it's not ok because has wrong date;
• row#4 it's not ok because has lower value;
• row#5 has wrong code;
• row#6 has wrong type;

this give picture of whole scene. can't wrap mind around how make these kind of inter-row checks in same table.

i hope explained myself bareley enought issue!

if understand correctly, can join:

select t.* t join      t tt      on t.code = tt.code ,         t.date = tt.date         t.type = 'type.b' ,         tt.type = 'type.a' ,         t.value > tt.value; 

or equivalently, exists:

select t.* t exists (select 1               tt               t.code = tt.code ,                     t.date = tt.date                     t.type = 'type.b' ,                     tt.type = 'type.a' ,                     t.value > tt.value              );