Need help querying a database to show latest versions

Question

I working with a mysql database with a table called Page. Is there any way to only pull the latest version from each page?

ID      Page Title      Version
0001    Homepage        1
0002    Homepage        2
0003    Homepage        3
0004    Contact         1
0005    Contact         2
0006    Basket          1
0007    Basket          2
0008    Basket          3
0009    Basket          4

What I need:

ID      Page Title      Version
0003    Homepage        3
0005    Contact         2
0009    Basket          4

Answer 1

You can use MAX and GROUP BY, as follows:

 SELECT MAX(ID)
        ,PageTtile
        ,MAX(version)
    FROM Page
    GROUP BY PageTitle

Answer 2

you can also use row_number() as follows

    SELECT * FROM (
SELECT Row_Number() over (partition by Title order by Version DESC) As rn, Page, Title, Version FROM Page) a
where rn = 1

Answer 3

Try this query -

SELECT t1.* FROM tbl t1
 JOIN (SELECT PageTitle, MAX(Version) Version FROM tbl GROUP BY PageTitle) t2
   ON t1.PageTitle = t2.PageTitle AND t1.Version = t2.Version;

Output:

3 Homepage  3
5 Contact   2
9 Basket    4