CODEWITHSUNDEEP
sqlsqlite
Robert
Robert

Reputation: 38223

How can I combine several 'MAXIMUM' SQLite queries into one query?

tblParents

parentId | childId
---------+-----
102      |  1
102      |  3
102      |  4
104      |  3
...

tblPopularity

Id | popularityScore
---+-----
1  | 4000
2  | 8000
3  | 3000
4  | 2000
...

I have a query that finds the most popular childId, given a parentId. I want to combine several queries together to get the most popular items of a set of parentItemId's, e.g. (102, 104, ...).

Desired output

parentId   | mostPopularChildId
           | i.e. the childId with the maximum popularityScore for the given parentId
-----------+-----
102        | 1
104        | 3
...

Upvotes: 0

Views: 57

Answers (2)

CL.
CL.

Reputation: 180270

In SQLite 3.7.11 or later, it is possible to get other columns from the record that matches a MIN/MAX:

SELECT parentId,
       childId AS mostPopularChildId,
       MAX(popularityScore)
FROM tblParents
JOIN tblPopularity ON tblParents.childId = tblPopularity.Id
GROUP BY parentId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

To do a query like this in SQLite, you can calculate the maximum score and then join back to the tables to get the id:

select pp.ParentId, po.Id, pp.maxscore
from (select pa.ParentID, max(PopularityScore) as maxscore
      from tblParent pa join
           tblPopularity po
           on pa.ChildId = po.Id
     ) pp join
     tblParent pa
     on pa.ParentId = pp.ParentId join
     tblPopularity po
     on pa.ChildId = po.Id and
        po.PopularityScore = pp.maxscore
where . . .

Upvotes: 1

Related Questions