MYSQL top N rows from multiple table join

Question

Like, there is top keyword in sql server 2005, how to select top 1 row in mysql if i have join on multiple table & want to retrieve extreme of each ID/column. Limit restricts the no. of row returns so it can't solve my problem.

Answer 1

If I understand you correctly, top doesn't solve your problem either. top is exactly equivalent to limit. What you are looking for is aggregate functions, like max() or min() if you want the extremes. for example:

select link_id, max(column_a), min(column_b) from table_a a, table_b b 
where a.link_id = b.link_id group by link_id

Answer 2

SELECT  v.*
FROM    document d
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    version v
        WHERE   v.document = d.id
        ORDER BY
                v.revision DESC
        ) v

or

SELECT  v.*
FROM    document d
LEFT JOIN
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY v.id ORDER BY revision DESC)
        FROM    version
        ) v
ON      v.document = d.id
        AND v.rn = 1

The latter is more efficient if your documents usually have few revisions and you need to select all or almost all documents; the former is more efficient if the documents have many revisions or you need to select just a small subset of documents.

Update:

Sorry, didn't notice the question is about MySQL.

In MySQL, you do it this way:

SELECT  *
FROM    document d
LEFT JOIN
        version v
ON      v.id = 
        (
        SELECT  id
        FROM    version vi
        WHERE   vi.document = d.document
        ORDER BY
                vi.document DESC, vi.revision DESC, vi.id DESC
        LIMIT 1
        )

Create a composite index on version (document, revision, id) for this to work fast.