How to join table with maximum value in column?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Use a sub-query to obtain the relevant Person per group.

SELECT *
    , (
        SELECT TOP 1 Name
        FROM People p
        WHERE p.GroupId = g.Id
        ORDER BY Priority DESC
    )
FROM GROUP g;