Selecting highest version number

[Drongo_III]

Hi Guys

 

I'm a bit stuck on a query and hoping someone can help. I'm sure this has a really simple solution.

 

Below is a simplified representation of a table and I'm trying to select all of the data but where there are instances of the same postID I want to only select the row with the highest version number.

 

So for instance in a select all on the table below I would expect to get IDs of 2,3 and 4.

 

 

id | postID | version

1     2           1

2     2           2

3     1           1

4     3           1

 

Any help is much appreciated. 

[Aptugo]

mmm, it would be:

 

SELECT max(id) FROM your_table GROUP BY postID

 

Cheers!

Edited August 12, 2014 by mac_gyver
link removed

[kicken]

Use a sub query to get the number of the most recent, then use that to join to the original table, or in your where clause condition.

Eg:

SELECT
  *
FROM table 
INNER JOIN (SELECT PostId, MAX(version) as version FROM table GROUP BY PostID) mr ON mr.PostId=table.PostId AND mr.version=table.version

Make sure you have an index on PostId,Version

[Drongo_III]

Thanks Kicken that worked perfectly. I need to go away and dissect this now to understand what's going on.