Help with select

[Drongo_III]

Hi Guys

 

I have a table which in it's shortened form has the following columns:

 

id  |  postID  |  title  |  content  |  version

 

The column for postID has a number that can be shared by multiple rows - differentiated by version number.

 

I want to run a query to select all records that are like a given keyword (i.e. %LIKE%) but where results share the same postID I only want to return the highest version number for that record.  

 

The difficulty is some records may have multiple version numbers that match the like statement and some may have only one. So this variance with the LIKE search is causing me some confusion.

 

I've tried this in a few ways using a sub-query but for the life of me I cannot work out how to do it.

 

Any help would be appreciated,

 

Drongo

 

[mac_gyver]

i suspect you actually want to find the matching row(s) with the highest version number (i.e. you want the latest content, not just the highest version number.)

 

see this link - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[Drongo_III]

i suspect you actually want to find the matching row(s) with the highest version number (i.e. you want the latest content, not just the highest version number.)

 

see this link - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

 

 

I'm still confused.

 

So if I wish to select all records that are like a particular keyword, but in instances where there are multiple records on the same postID only return the highest version number, would I do something like this?

SELECT * FROM table t1 WHERE title LIKE '%SOME-TITLE%' AND version = (SELECT MAX(version) FROM table t2 WHERE title LIKE '%SOME-TITLE%');

I don't have the database in front of me at the moment to test it although I'm not sure I quite understand how to construct the query for what I am trying to achieve.

[Barand]

Your subquery should be finding the max version for the post_id, not the title. Unfortunately, given three solutions in that link, you opted for the first one which the manual clearly states is inefficient. Personally, I prefer the second option given.

 

When you use an INNER JOIN between two table the query only returns rows where there is a match in both tables. So if you had a table that contained the post_id and the max version for the post_id you could match your table against it so you only see those with the latest version.

 

You can create this second table, not as a physical table in your db but as a "logical" table, by using a table subquery.

(SELECT post_id
  , MAX(version) as version
FROM table 
GROUP BY post_id) as maxv

Now join your table to the maxv table in your query matching on post_id and version

SELECT id
 ,  postID  
 ,  title  
 ,   content  
 ,  version
FROM table t1
    INNER JOIN (
        SELECT post_id
          , MAX(version) as version
        FROM table 
        GROUP BY post_id
        ) as maxv USING (post_id, version)
WHERE title LIKE '%SOME_TITLE%'