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.

Improve this question
3
  • 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? Commented Aug 1, 2018 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? Commented Aug 1, 2018 at 17:37
  • I am still waiting on the business side to decide that. The are determining a precedence order. Commented Aug 1, 2018 at 18:06

2 Answers 2

Reset to default
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
Improve this answer
3
  • 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! Commented Aug 2, 2018 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! Commented Aug 3, 2018 at 20:29
  • This solution worked for me. Thanks for the assist! One day, I will just know this stuff. Commented Aug 6, 2018 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 ;
Improve this answer
2
  • 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. Commented Aug 1, 2018 at 17:12
  • These both return C for Manson, not B as the OP requires. Commented Aug 1, 2018 at 17:28

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.