ineztia ineztia - 5 months ago 7
SQL Question

Join tables with distinct highest ranked row

I have three tables defined like this:

[tbMember]
memberID | memberName
1 | John
2 | Peter

[tbGroup]
groupID | groupName
1 | Alpha
2 | Beta
3 | Gamma

[tbMemberGroupRelation]
memberID | groupID | memberRank (larger number is higher)
1 | 1 | 0
1 | 2 | 1
2 | 1 | 5
2 | 2 | 3
2 | 3 | 1


And now I want to perform a table-join selection to get result which contains (distinct) member with his highest ranked group in each row, for the given example above, the query result is desired to be:

memberID | memberName | groupName | memberRank
1 | John | Beta | 1
2 | Peter | Alpha | 5


Is there a way to implement it in a single SQL like following style ?

select * from tbMember m
left join tbMemberGroupRelation mg on (m.MemberID = mg.MemberID and ......)
left join tbGroup g on (mg.GroupID = g.GroupID)


Any other solutions are also appreciated if it is impossible to write in a simple query.


========= UPDATED =========

Only ONE highest rank is allowed in table

Answer

One solution would be to create an inverted sequence/rank of the memberRank so that the highest rank per member is always equal to 1.

This is how I achieved it using a sub-query:

SELECT
    m.memberID,
    m.memberName,
    g.groupName,
    mg.memberRank
FROM
    tbMember m
LEFT JOIN
    (
    SELECT
        memberID,
        groupID,
        groupName,
        memberRank,
        RANK() OVER(PARTITION BY memberID ORDER BY memberRank DESC) AS invRank
    FROM
        tbMemberGroupRelation
    ) mg
    ON (mg.memberID = m.memberID)
    AND (mg.invRank = 1)
LEFT JOIN
    tbGroup g
    ON (g.groupID = mg.groupID);
Comments