Re: mysql code questions

On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellerer<spam_eater(at)gmx(dot)net> wrote:
> Ray Stell wrote on 12.08.2009 20:19:
> I would probably do it this way:
>
> SELECT tt. *
> FROM testtable tt
> WHERE create_date = (SELECT MAX(create_date)
>                     FROM testtable tt2
>                     WHERE tt.id = tt2.id);

The article is *very* unclear about the problem. You have to read the
whole schema and their proposed solution to figure out what the
problem they're trying to solve is. This query doesn't solve it.

They want the newest record for *each* id. Ie, the equivalent of
"select id,max(create_date) from tt group by id" except including the
other attributes for the record with that date.

There are a number of ways to solve it. Postgres supports all of the
solutions in the comments including the one proposed in the post as
well as the window-function based solution.

It also supports an extension which could be even more efficient than
all of them:

select DISTINCT ON (id) from tt order by id,create_date desc

If you have an index on <id,create_date desc> (Or if you have an index
on <id,create_date> and use "order by id desc, create_date desc" in
the query) then this could do a single index scan.

--
greg
http://mit.edu/~gsstark/resume.pdf