Trouble with query to get most recent version of an element

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm new to mysql and I was wondering if I could trouble some of you for
help.  I have a table in my database that may contain multiple versions
of the same element as different rows.

I wanted to use the following query to group the rows by element with
"group by" and then use the "having" to get the most recent version of
each element, which would then be returned in "select" but this doesn't
seem to be working.  Any elements with multiple versions are simply not

I could swear that I have used a similar approach in the distant past
with other databases and it worked, though, so I must be bollocksing
this up pretty badly while attempting to work off of what I any case, any help on understanding why this isn't
working would be appreciated.

       element_id -- not unique!
group by
      created_on = max(created_on)

Re: Trouble with query to get most recent version of an element

Quoted text here. Click to load it

The HAVING clause creates criteria for restricting groups from the query,
not for restricting individual rows within the groups.  It's useful for
doing things like HAVING MAX(created_on) > '2005-12-31'.  Row restrictions
are done in the WHERE clause, but unfortunately, aggregate functions like
MAX cannot be used in the WHERE clause.

Quoted text here. Click to load it

Here's a different solution, using an outer join trick.  This fetches
elements for which there is no other element with the same id and a greater
created_on date.

SELECT e.element_id, e.created_on,, e.description
FROM elementversions AS e LEFT OUTER JOIN elementversions AS egreater
  ON e.element_id = egreated.element_id AND e.created_on <
WHERE egreater.element_id IS NULL

If the egreater.element_id is NULL, it means nothing matched on the right
side of our join.  If there is no record with a greater created_on date,
then `e` must be the record with the greatest created_on for the given

Bill K.

Re: Trouble with query to get most recent version of an element

rubikzube* wrote:

Quoted text here. Click to load it

how about:

select a.element_id,a.created_on,,a.description from elementversions
(select b.element_id,max(b.created_on) as created_on from elementversions
b) c
where a.element_id=c.element_id and a.created_on=c.created_on;

if you tried to do this by just adding the max(created_on), you could get
multiple records returned as you would need to include name and
description in the group by.

Site Timeline