* Ville Mattila
> 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.
Take a look at this:
<URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >
It can also be done with joins in some cases, basically joining the status
table twice, something like this:
SELECT P.*,S1.*
FROM projects P
LEFT JOIN status_info S1 ON
S1.PID = P.PID
LEFT JOIN status_info S2 ON
S2.PID = P.PID AND
S2.datecol > S1.datecol
WHERE
ISNULL(S2.PID)
This will also list projects with no status info. If you don't want this,
change the first LEFT JOIN to a normal join.
When I say "in some cases" it is because this can be very heavy on the
server if there are many status_info rows per project. 'Many' in this case
means hundreds, but this will depend on the server & the data.
--
Roger
