Geek Answers Handbook

MYSQL top N rows from multiple table joins

Like, there is a top keyword in sql server 2005, how to select the top 1 row in mysql if I join several tables and want to get the last ones from each ID / column. The limit limits the number. strings, so it cannot solve my problem.

+5
source share
2 answers
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 effective if your documents usually have several changes, and you need to select all or almost all of the documents; the first is more effective if the documents have many changes or you need to select only a small subset of the documents.

Update:

Sorry, did not notice the question about MySQL.

MySQL :

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
        )

version (document, revision, id), .

+3

, . . , , - , max() min(), . :

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
0

More articles:


All Articles