How can I use a SELECT clause based on comparison between values of different rows? For instance, let Table 1 be:
ID fieldA fieldB fieldC 1 A X 2 2 A Y 3 3 B X 3 4 B X 2 5 C X 2
for each diffent value of fieldA, I want to get only the rows where combination of fieldC for fieldB=Y is greater then fieldC for fieldB=X. Note:There is a maximum of two rows for each value of fieldA.
ID fieldA fieldB fieldC 1 A X 2 2 A Y 3 3 B X 3 5 C X 2
I was trying to use OVER clause, but I couldn't make it to work so far...
Thank you for your help!