CODEWITHSUNDEEP
mysqljoininner-join
krx
krx

Reputation: 2683

Inner join a single row by getting only the highest integer row

When I inner join the second table I only want to join a single row. The criteria is to take the row with the highest integer in the level column. I was thinking something like this:

SELECT *
FROM file_repo
INNER JOIN
(   SELECT 
       MAX(files.level)
    FROM
       files
) ON file_repo.id = files.repo_id

For each row in file_repo I will get a single row from files with the highest value in the level column.

Upvotes: 2

Views: 5765

Answers (2)

Quassnoi
Quassnoi

Reputation: 425391

SELECT  *
FROM    file_repo fr
JOIN    files f
ON      f.id = 
        (
        SELECT  id
        FROM    files fi
        WHERE   fi.repo_id = fr.id
        ORDER BY
                repo_id DESC, level DESC, id DESC
        LIMIT 1
        )

Create an index on files (repo_id, level, id) for this to work fast.

This will handle duplicates on level correctly.

Upvotes: 5

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

SELECT fr.*, fm.MaxLevel
FROM file_repo fr
INNER JOIN (   
    SELECT repo_id, MAX(level) as MaxLevel
    FROM files 
    GROUP BY repo_id
) fm ON fr.id = fm.repo_id

Upvotes: 6

Related Questions