Jump to content

Help with select


Drongo_III

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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%'
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.