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!

  • if you add another row such as (6, 'C', 'Y', 1), what will the result look like?
    – jyao
    Apr 12 '18 at 17:25
  • 1
    what rdms are you using?
    – scsimon
    Apr 12 '18 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 '18 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 '18 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 '18 at 18:14

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

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, privacy policy and cookie policy

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