0

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!

5
  • if you add another row such as (6, 'C', 'Y', 1), what will the result look like?
    – jyao
    Apr 12, 2018 at 17:25
  • 1
    what rdms are you using?
    – S3S
    Apr 12, 2018 at 17:53
  • Why are rows with ID 3 and 5 in the result? There is no fieldB= 'Y' for those fieldA values (B and C). Apr 12, 2018 at 18:45
  • jyao, then you wouldn't get this row in the results, since fieldC (for fieldB=Y) < fieldC (for filedB=x)
    – Fábio
    Apr 13, 2018 at 18:12
  • ypercubeᵀᴹ, you can have one or two rows of the same value for fieldA. If there is only one row, it'd appear in the results regardless of the other columns
    – Fábio
    Apr 13, 2018 at 18:14

1 Answer 1

0

-- 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.