Wählen Sie Abfrage basierend auf Priorität zu einer bestimmten characterfolge aus

Ich habe einen Tisch:

ID|Expense|Name 1|1000|John 1|1000|Roger 1|1000|Megan 2|500|Sally 2|500|John 3|700|Jen 3|700|Mike 

Ich möchte durch id und Kosten gruppieren, aber will nur eine einzelne Zeile. Auch würde ich gern Vorliebe für John bei der Auswahl und jede randome eine Zeile, wenn John nicht vorhanden ist. (für id = 3 Jen oder Mike ist akzeptabel)

Gewünschtes Ergebnis:

 ID|Expense|Name 1|1000|John 2|500|John 3|700|Jen 

Ich habe versucht mit Rank , aber ich bin nicht in der Lage, gegen die ids ohne John zu unterscheiden.

Bitte helfen Sie

Vielen Dank!

Dies kann mit row_number mit Priorisierung mit case expression erfolgen.

 select id,expense,name from ( select t.*,row_number() over(partition by id order by case when name='John' then 1 else 2 end) as rnum from tbl t ) t where rnum=1 

Eine weitere Option ohne zusätzliche Felder ist die Verwendung der WITH TIES- Klausel

Beispiel

 Declare @YourTable Table ([ID] int,[Expense] int,[Name] varchar(50)) Insert Into @YourTable Values (1,1000,'John') ,(1,1000,'Roger') ,(1,1000,'Megan') ,(2,500,'Sally') ,(2,500,'John') ,(3,700,'Jen') ,(3,700,'Mike') Select Top 1 with ties * from @YourTable Order By Row_Number() over (Partition By ID,Expense Order By NullIf(Name,'John')) 

Kehrt zurück

 ID Expense Name 1 1000 John 2 500 John 3 700 Jen