Sql-server – Getting only the necessary row

sql serversql-server-2016t-sql

I am working on a query for a project and sometimes the results have multiple rows for the same entity. What I need to do is:

If the results look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeA       |      M. Manson

   TypeB       |      M. Manson

   TypeC       |      M. Manson

Then I need to choose TypeB over TypeA or TypeC for M. Manson

My results need to look like this:

   Type        |   Entity
-----------------------------
   TypeA       |      B. Didly

   TypeA       |      J. Hendrix

   TypeC       |      G. VanFleet

   TypeB       |      M. Manson

I am struggling with using a cursor or finding another option.

Basically if an entity has all three types, I need to only get TypeB for that entity.

There are 10 possible "types" from what I have been told. And the client has given me their ranking of those types. I am thinking a case statement to set the rank.

Best Answer

Assuming you also only want B when an entity has two types, or if they have A and C you don't care which one you get:

;WITH x AS 
(
  SELECT type, entity, rn = ROW_NUMBER() OVER 
    (
     PARTITION BY entity 
     ORDER BY CASE type WHEN 'B' THEN 1 ELSE 2 END
    )
  FROM dbo.tablename
)
SELECT type, entity 
  FROM x 
  WHERE rn = 1;

If you care about the order of A and C in the case where those are the only two (in this example A will be returned):

     ORDER BY CASE type WHEN 'B' THEN 1 WHEN 'A' THEN 2 ELSE 3 END
Related Question