View population based on inter-row conditions

Question

I'm trying to create and populate a view but there's some tricky criteria that's slowing me a lot. All of the data flowing in are coming from other tables in the DB.

I need to pick a row only if another row (of the same table) comply to given constrains: for example, given a record, I'll take it if:

• It shares the CODE with another record in the same table;
• It has a certain TYPE;
• It's DATE matches the one with another TYPE but the same CODE;
• It's VALUE is bigger then the 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 the records above, I would only take row #2 because it shares the CODE with a row of TYPE "A" (restrain 1), it has a certain TYPE "B" (R2), it has matching date with said record (R3), and its VALUE is bigger the the matching record (300>200).

• row #3 it's not ok because it has the wrong date;
• row#4 it's not ok because it has a lower value;
• row#5 has the wrong CODE;
• row#6 has the wrong TYPE;

This was just to give you a picture of the whole scene. What I can't wrap my mind around is how to make these kind of inter-row checks in the same table.

I hope I explained myself bareley enought for you to get the issue!

Answer 1

If I understand correctly, you can do this with a join:

select t.*
from t join
     t tt
     on t.code = tt.code and
        t.date = tt.date
        t.type = 'Type.B' and
        tt.type = 'Type.A' and
        t.value > tt.value;

Or equivalently, exists:

select t.*
from t
where exists (select 1
              from tt
              where t.code = tt.code and
                    t.date = tt.date
                    t.type = 'Type.B' and
                    tt.type = 'Type.A' and
                    t.value > tt.value
             );