Select First Row Of Group With Criteria

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

Solution 1:

You can use ROW_NUMBER like this:

SELECT FieldA, FieldB, FieldC
FROM (
   SELECT FieldA, FieldB, FieldC,
          ROW_NUMBER() OVER (PARTITION BY FieldA
                             ORDERBYCASEWHEN FieldC = 'X' THEN 1ELSE2END,
                                      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.