0

I have a query where I do a group by on a version field.

The thing is I want to select the record with the highest version number (aka most recent record).

This is what my database looks like:

productinformation:

producinformation table

productinformation_content:

enter image description here

This is what my query looks like:

SELECT *
FROM productinformation
LEFT JOIN productinformation_content
    ON productinformation_content_sourceid = productinformation_id
WHERE productinformation_content_lang = 'en' AND
      productinformation_content_active = 1
GROUP BY productinformation_content_sourceid
ORDER BY productinformation_content_title ASC

There can be multimple records with the same productinformation_content_sourceid so I grouped on that field but I need the record with the highest productinformation_content_version of that group

Can anyone help me out?

Any help is highly appreciated.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Frank Lucas
  • 551
  • 3
  • 11
  • 25
  • You have date create and update. Why don't you use these columns to order by? – devil_coder May 22 '17 at 13:53
  • 1
    I'm afraid that your question, as it stands now, will likely not be helpful to anyone else, and may not even be answerable. Please _edit_ your question and show sample data for the two tables along with the expected output. – Tim Biegeleisen May 22 '17 at 13:53
  • Could you order by the version too? `ORDER BY productinformation_content_version DESC, productinformation_content_title ASC` – Jim Wright May 22 '17 at 13:53
  • @JimWright I tried that, unfortunately it doesn't work – Frank Lucas May 22 '17 at 13:54
  • What output do you get? Trying swapping the columns around in the `ORDER BY` statement. I've edited my previous comment. – Jim Wright May 22 '17 at 13:55
  • @JimWright I've tried that also and still doesn't work :) I get same results maybe it has to do something with the left join? – Frank Lucas May 22 '17 at 13:57
  • I don't know how why this is marked as duplicate I tried the solutions in the marked question but nothing works. – Frank Lucas May 22 '17 at 14:13
  • Your question does not indicate in any way that you are aware of the duplicate question and how you tried to implement any of the solutions offered there. This is why your question is a duplicate: you asked the same question with different data. – Shadow May 22 '17 at 15:20

0 Answers0