CODEWITHSUNDEEP
mysqlgreatest-n-per-group
tnort173
tnort173

Reputation: 368

Select ID of a row with max value

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

Upvotes: 0

Views: 907

Answers (2)

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 1

tnort173
tnort173

Reputation: 368

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!

Upvotes: 0

Related Questions