From: Greg Stark <gsstark@mit.edu>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: mysql code questions
Date: Wed, 12 Aug 2009 20:00:38 +0100
Message-ID: <407d949e0908121200i5a732ec0u3e991c4f47393f82@mail.gmail.com> (raw)
In-Reply-To: <h5v31e$tqn$1@ger.gmane.org>
References: <20090812181928.GF7527@cns.vt.edu>
<h5v31e$tqn$1@ger.gmane.org>
On Wed, Aug 12, 2009 at 7:52 PM, Thomas Kellerer<spam_eater@gmx.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, spam_eater@gmx.net
Subject: Re: mysql code questions
In-Reply-To: <407d949e0908121200i5a732ec0u3e991c4f47393f82@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox