Clever Geek Handbook

Select a query based on priority for a specific row

I have a table:

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

I want to group by id and expense, but only want one row. Also, I would like to give preference to John when selecting and any random one line if John is not present. (for id = 3 any Jen or Mike is allowed)

Desired output:

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

I tried to use Rank

but I can't tell the difference from non-John IDs.

Please, help

Thank!

+3


source to share


2 answers


This can be done with row_number

precedence using an expression case

.



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
+6


source


Another option without extra fields is WITH TIES clause

Example

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'))


Returns

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


source






More articles:


All Articles