Hi Tom, I don't think there is a straight way in Mysql to accomplish this. It would have been great if Mysql included this provision.
I suggest you to use temporary tables. First make a temporary table inserting values of job and the maximum date of inv for the job. Create temporary table temp1 select max(date) dt , job from tbl group by job; Now create another temp table with vaulues of job and maximum date but which is not in the temporary table created first. Outer join can be used here. Create temporary table temp2 select max(date) dt, job from tbl a left join temp1 b on a.job = b.job and a.date = b.dt where b.job is null group by job; Insert one temp into the other (insert into temp1 select * from temp2). Querying this table along with the original tables will yield the result. I hope there would be more elegant solution to this problem. Note : The date, job combination should be unique for this to work correctly. Anvar. At 09:22 AM 29/11/2001 -0800, you wrote: >I'm trying to write a query that will limit the results but not by the total >amount returned like a normal LIMIT. It's an invoice database and I would >like to return the last to invoices for a certain job. So if there are 100 >jobs with several invoice dates I would like to return a web page displaying >a table of invoice dates ordered by job name and LIMIT the invoice dates to >the last two invoice dates for each job. Here's my query so far; > >SELECT site_service.id, site_service.service_date, >site_service.invoice_date, site.site_id, site.name, site.state, site.status >FROM site_service, site WHERE site_service.site_id = site.id AND >site.company = 'Nextel' > >If I add LIMIT 2, I only get two invoices. I'm trying to get the last to >invoices for each site. > >Is this possible or is there another direction I should go? I checked out >the MySQL site without any luck. > >Thanks, >Tom > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
