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 want just a single row. Also, I would like to give preference to John while selecting and any random one row if John is not present. (for id=3 any Jen or Mike is acceptable)
Desired outcome:
ID|Expense|Name
1|1000|John
2|500|John
3|700|Jen
I tried using Rank, but I am not able to differentiate against the ids without John.
Please Help
Thanks!