0

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.

  • Can you ever have more than one A or B or C for the SAME entity? In other words, if there are multiple rows for the same entity, will those multiple rows always be A and B and C? What are the rules? – Scott Hodgin Aug 1 '18 at 17:26
  • For the correct solution, your question should also define what happens when an entity only has A and C. Does one of those win? Do you not care? – Aaron Bertrand Aug 1 '18 at 17:37
  • I am still waiting on the business side to decide that. The are determining a precedence order. – Bill Hughes Aug 1 '18 at 18:06
1

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
  • I should be getting the final details from the client today. I will give this a try hopefully today and let you know how it goes. Thanks for your input! – Bill Hughes Aug 2 '18 at 16:19
  • I think this may be the solution. I am sending the results to my users and they will let me know if we are good. Thanks for the suggestion! – Bill Hughes Aug 3 '18 at 20:29
  • This solution worked for me. Thanks for the assist! One day, I will just know this stuff. – Bill Hughes Aug 6 '18 at 13:43
0

Complete re-edit of my answer based on my deleted comment, "And why does TypeA appear twice in your sample output?"

create table testx (type varchar(10), entity varchar(20)) ;

insert into testx (type, entity)
select 'A' , 'Didly' union all
select 'A' , 'Hendrix' union all
select 'C' , 'VanFleet' union all
select 'A' , 'Manson' union all
select 'B' , 'Manson' union all
select 'C' , 'Manson' ;

 select * from
 (
 select a.type, b.entity, b.ent_count,
 row_number() over (partition by b.entity order by b.entity) as rownum
 from testx a
 inner join
    (select entity, count(*) as ent_count 
     from testx 
     group by entity
    ) b
  on a.entity = b.entity
 )y
 where ent_count = 1 or rownum = 3 ;

---- Much simpler, this time using count(*) as an analytic function ---

 select * from
 (
  select a.type, entity,
 row_number() over (partition by entity order by entity) as rownum,
 count(*) over (partition by entity order by entity) as ent_count
 from testx a
 )x
 where ent_count = 1 or rownum = 3 ;
  • Thanks! I give this a try and see how it goes. I thought I might have to get row_number involved, but I don't have too much experience with it. – Bill Hughes Aug 1 '18 at 17:12
  • These both return C for Manson, not B as the OP requires. – Aaron Bertrand Aug 1 '18 at 17:28

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.