Re: Join only the latest entry...
* 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: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join only the latest entry...
On Wed, 03 Dec 2003 11:07:28 +0200 Ville Mattila <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join only the latest entry...
- Original Message -
From: "Ville Mattila" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 03, 2003 4:07 AM
Subject: Join only the latest entry...
> Hello there,
>
> 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.
Have you tried a straightforward join? Something like
SELECT table1.name FROM table1, table2
WHERE table1.id = table2.entryid AND
{expressing constraining the date in table2 to be fairly
recent}
>
> Thanks,
> Ville
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join only the latest entry...
Hello there, 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. Thanks, Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
