Select first row of group with criteria

Question

I have a table in this format:

FieldA   FieldB   FieldC
1111     ABC      X
1111     DEF      Y
1111     GHI      X
2222     JKL      Y
2222     MNO      X
3333     PQR      U
3333     STT      U

I want to select one FieldB per FieldA with preference to X in FieldC (if there no X, pick another one).

I've tried using the RANK function with PARTITION BY but I find it too inconsistent and I have now reached a wall.

My output would look like this:

 FieldA   FieldB   FieldC
    1111     ABC      X
    2222     MNO      X
    3333     PQR      U

Query:

Select
rank() over (partition by Field3 order by Field1),
Field,1 Field2, Field3
FROM table
ORDER BY Field1, Field3

I'm guessing I'd need to put that query inside a sub-query...

if you have 2 `X`'s should you get both of them? and if there is none,what's the other value to be prioritized? — Vamsi Prabhala, Apr 12 '17 at 13:33

Giorgos Betsos · Accepted Answer · 2017-04-12T13:50:44.433

3

You can use ROW_NUMBER like this:

SELECT FieldA, FieldB, FieldC
FROM (
   SELECT FieldA, FieldB, FieldC,
          ROW_NUMBER() OVER (PARTITION BY FieldA
                             ORDER BY CASE 
                                         WHEN FieldC = 'X' THEN 1
                                         ELSE 2
                                      END,
                                      FieldB) AS rn 
   FROM mytable) AS t
WHERE t.rn = 1

The above query picks one record out of each FieldA partition. It prioritizes records having FieldC = 'X' over all other records.

edited Apr 12 '17 at 13:50

answered Apr 12 '17 at 13:36

Giorgos Betsos

71,379
9
63
98

Absolutely brilliant! – nickfrenchy Apr 12 '17 at 13:59

Select first row of group with criteria

1 Answers1

Linked