Re: Join only the latest entry...

[Skippy]

On Wed, 03 Dec 2003 11:07:28 +0200 Ville Mattila
<ville.mattila@stripped> wrote:
> I have a table including information about my projects, the structure 
> has each id and name. Then I have another table including status 
> information of each project: entryid, projectid, status and timestamp.
> 
> Is there any possibility to fetch a list of projects with the most 
> recent status by one query? I can do it of course by two different 
> queries, but I don't find it as very good solution.

I think this is similar to my recent question: you have a table where
for one criteria (project) you can have more than one entry and you want
a listing to return only the most [enter your second criteria here, in
your case latest] one.

As long as the second criteria is not something easy to put in a where
clause*, i'm afraid the answer is the same one I got: upgrade to 4.1 and
try to use subselects; or read all the data and process it in the client
code; or do subsequent queries "by hand" to refine the output.

*) By something NOT easy to put in a where clause I mean something like
the most recent, the smallest, and so on. It would be nice if the WHERE
clause would accept a max() or something along those lines (like saying
"give me these rows WHERE column is the biggest/smallest"). :) But I
guess that's what subselects were meant for.

-- 
Skippy - Romanian Web Developers - http://ROWD.ORG