SELECT clausebased on other rows values 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!
-- Please try this query

create table #tablea (id int, fieldA char(1), fieldB char(1), fieldc int)
insert into #tablea values
(1,'A','X', 2)
,(2,'A','Y',3)
,(3,'B','X',3)
,(4,'B','X',2)
,(5,'C','X',2)

select t1.*
from #tablea t1
inner join (
    select fieldA, fieldB, max(fieldc) fieldc
    from #tablea
    group by fieldA, fieldB
)t2 on t1.fieldA = t2.fieldA
and t1.fieldB = t2.fieldB
and t1.fieldc = t2.fieldc
Order by Id