SQL selecting from a particular duplicate row

Question

Hi all I have a sql query

SELECT  FKid1, FKID2, a, b, c
from [source]
where FKID1 = 3

which returns the following data set so (hope formatting holds)

   FKID1   FKID2   A    B    C
    3   40297    0      0   2
    3   40297    0      100 1
    3   40325    0      0   2
    3   40325    0      0   3
    3   40325    0      10  -1
    3   40348    0      10  3
    3   40391    0      10  -1
    3   40392    0      10  -1
    3   40501    0      10  -1
    3   40501    0      0   2

I'm trying to improve this query so that if there are 2 rows with duplicate FKID1 and FKID2 values, it will pick the column B value from a particular row as follows...

if there is a row with C = -1 use the B value in this row and ignore others. if there are 2 rows with C <> -1 then pick the MAX(B) value. For rows that are not duplicated, return as normal.

IE the results should look as follows...

  FKID1   FKID2   A    B    C
    3   40297    0      100 1
    3   40325    0      10  -1
    3   40348    0      10  3
    3   40391    0      10  -1
    3   40392    0      10  -1
    3   40501    0      10  -1

the correct values selected for the B column and no dupes.

We have a solution at the moment, but I think it's overcomplicated and wondered if anyone had any ideas?

Thanks.

Answer 1

One way

;WITH cte As
(
SELECT  FKid1, FKID2, a, b, c,
ROW_NUMBER() OVER (PARTITION BY FKID1, FKID2
                   ORDER BY CASE WHEN C = -1 THEN 0 ELSE 1 END ASC, B DESC) AS RN
from [source]
where FKID1 = 3
)
SELECT  FKid1, FKID2, a, b, c 
FROM cte  
WHERE RN=1

Answer 2

Select T1.FKID1, T1.FKID2
    , Min(A) As A
    , Case
        When Count(*) = 2 And NegCB.B Is Not Null Then Max(NegCB.B)
        Else Max(B)
        End As B
    , Min(C) As C
From Source As T1
    Outer Apply (
                Select T3.B
                From Source As T3
                    Where T2.FKID1 = T1.FKID1
                        And T2.FKID2 = T1.FKID2
                        And T2.C = -1
                ) As NegCB
Where FKID1= 3
Group By T1.FKID1, T1.FKID2