This seems like a fairly simple SQL statement, but I’m just not seeing it. I have:
- a table called Groups. columns are Id, Name, Address, and a bunch of other columns we don’t care about.
- a table called People. columns are Id, GroupId, Name, Priority, and a bunch of other columns we don’t care about.
I want a list of all Groups. each row in this list needs to have the Id, Name, and Address of the Group. It also needs to have the Name of the person with the highest Priority. Priority can be NULL as well, which would be selected if there was no other person in that group with a non null. If there are any ties for Priority, I don’t care which one gets selected as long as its one of them.
Sample data:
Group: (Id, Name, Address)
1, Group A, Address A
2, Group B, Address B
3, Group C, Address C
4, Group D, Address D
People: (Id, GroupId, Name, Priority)
1, 1, Alice, 39
2, 1, Bob, 22
3, 1, Craig, 88
4, 2, David, NULL
5, 2, Elise, 3
6, 3, Frank, NULL
Results should be:
1, Group A, Address A, Craig
2, Group B, Address B, Elise
3, Group C, Address C, Frank
4, Group D, Address D, NULL
I can use SELECT * FROM People ORDER BY Priority DESC to get the rows in the right order, but I’d need to only get the first row for each GroupId from that.
>Solution :
The other answer will also work, but this is useful if you also want to, say, know both the person and the priority of that person (In my experience this kind of thing is common):
SELECT Id, Name, Address, Person, Priority
FROM
(
SELECT g.Id, g.Name, g.Address, p.Name Person, p.Priority,
row_number() OVER (PARTITION BY g.ID ORDER BY p.Priority DESC) rn
FROM [Group] g
INNER JOIN People p ON p.GroupID = g.ID
) t
WHERE rn = 1