Trouble with query to get most recent version of an element

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
returned.

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
remember...in any case, any help on understanding why this isn't
working would be appreciated.

select
element_id -- not unique!
,created_on
,name
,description
from
elementversions
group by
element_id
having
created_on = max(created_on)

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

"rubikzube*" wrote in message
news:1143034707.562102.282790@i39g2000cwa.googlegroups.com.. .
> 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

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.

> select
> element_id -- not unique!
> ,created_on
> ,name
> ,description
> from
> elementversions
> group by
> element_id
> having
> created_on = max(created_on)

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.name, e.description
FROM elementversions AS e LEFT OUTER JOIN elementversions AS egreater
ON e.element_id = egreated.element_id AND e.created_on <
egreater.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
element_id.

Regards,
Bill K.

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

rubikzube* wrote:

> 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
> returned.

> 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
> remember...in any case, any help on understanding why this isn't
> working would be appreciated.

> select
> element_id -- not unique!
> ,created_on
> ,name
> ,description
> from
> elementversions
> group by
> element_id
> having
> created_on = max(created_on)

how about:

select a.element_id,a.created_on,a.name,a.description from elementversions
a,
(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.