SQL Query in a table but same id

Question

this ScreenShot is Lists on Sharepoint Database.

i Want to take data from database but i have version problem;

tp_ID is My List item ID tp_Version is My List Version

i want to take from data that higher version of tp_ID.

Thank You.

Answer 1

this will give u exactly what u required :

select  * from (select  *,RANK () OVER ( PARTITION BY  tp_id ORDER BY   tp_version DESC) AS rnk
        from    yourtable
        ) mytable where rnk <= 1