group by
| From: | blackwater dev | Date: | Wed, 25 May 2005 01:09:46 +0000 |
| Subject: | group by | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-39535@lists.php.net to get a copy of this message | ||
Hello,
Let's say I have an app used in car garages. I have two tables:
table cars
id
make
model
table work_done
id
carid
details
work_date
I need to pull out the last work order for each car. This pulls them all:
select c.make, c.model, c.id, wd.details from cars c join work_done wd
on wd.carid=c.id
I can do this to get one per car:
select c.make, c.model, c.id, wd.details from cars c join work_done wd
on wd.carid=c.id order by c.id
but that doesn't always pull the most recent one. How can I group by
carid so I only get one row returned per car and ensure that row
contains the most recent work row? I can't used subqueries as I
haven't updated to MySQL 4.1 yet.
Thanks!