Select ID of a row with max value

Question

How can I select the ID of a row with the max value of another column in a query that joins multiple tables?

For example, say I have three tables. tblAccount which stores a grouping of users, like a family. tblUser which stores the users, each tied to a record from tblAccount. And each user can be part of a plan, stored in tblPlans. Each plan has a Rank column that determines it's sorting when comparing the levels of plans. For example, Lite is lower than Premium. So the idea is that each user can have a separate plan, like Premium, Basic, Lite etc..., but the parent account does not have a plan.

How can I determine the highest plan in the account with a single query?

tblAccount

PKID	Name
1	Adams Family
2	Cool Family

tblUsers

PKID	Name	AccountID	PlanID
1	Bob	1	3
2	Phil	2	2
3	Suzie	2	1

tblPlans

PKID	Name	Rank
1	Premium	3
2	Basic	2
3	Elite	4
4	Lite	1

Here's the result I'm hoping to produce:

AccountID	Name	HighestPlanID	PlanName
2	Adams Family	1	Premium

I've tried:

SELECT U.AccountID, A.Name, MAX(P.Rank) AS Rank, P.PKID as HighestPlanID, P.Name as PlanName
FROM tblPlans P
INNER JOIN tblUsers U ON U.PlanID = P.PKID
INNER JOIN tblAccounts A ON U.AccountID = A.PKID
WHERE U.AccountID = 2

and the query will not always work, selecting the MAX of Rank does not select entire row's values from tblPlans.

I am looking for a solution that is compatible with mysql-5.6.10

@bill-karwin - After reviewing `Retrieving the last record in each group - MySQL`, I believe my question is a bit different in that it involves multiple tables. The answers in the linked question explain joining on the same table, which I cannot do here — tnort173, May 21 '21 at 15:42
I have reopened the question, but I suggest that the technique is the same even for joined tables. Follow the [tag:greatest-n-per-group] tag, this general type of question has been asked many times, and there is undoubtedly a case involving joined tables. — Bill Karwin, May 21 '21 at 15:45

The Impaler · Answer 1 · 2021-05-21T17:05:46.133

1

You can join the tables and use ROW_NUMBER() to identify the row you want. Then filtering is ieasy.

For example:

select *
from (
  select a.*, p.*,
    row_number() over(partition by a.pkid order by p.rank desc) as rn
  from tblaccount a
  join tblusers u on u.accountid = a.pkid
  join tblplans p on p.pkid = u.planid
) x
where rn = 1

Inside the subquery you can add where u.accountid = 2 to retrieve a single account of interest, instead of all of them.

edited May 21 '21 at 17:05

answered May 21 '21 at 16:01

The Impaler

45,731
9
39
76

Ah shoot - I should have mentioned I am using `mysql-5.6.10`. `partition by` and `row_number` is not available for the version I am using – tnort173 May 21 '21 at 16:05
Aha! I took your answer and reworked it slightly, most credit to you :D – tnort173 May 21 '21 at 16:17

score 0 · Answer 2 · answered May 21 '21 at 16:19

With the help of @the-impaler, I massaged their answer a bit and came out with something very similar:

select *
from (
  select a.*, p.*
  from tblaccount a
  join tblusers u on u.accountid = a.pkid
  join tblplans p on p.pkid = u.planid
  where u.accountid = 2
  order by p.rank desc
) x limit 1

The subquery sorts each user by plan rank from top to bottom, and then the top level query selects the top most row with limit 1. It seems to work!

Select ID of a row with max value

2 Answers2