group by

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=3Dc.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=3Dc.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!

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: GROUP BY

I figured out last night’s query .... and it’s a dozy.

The sub query

SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE
`verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY
`verse_of_the_day_Bible_trivia`.`date_added` DESC

puts the questions into descending order making the INNER JOIN link with the
most recently added trivia question in each category. This gives me the
desired results, I don't know if there is a way to stream line this or not.



SELECT Bible_trivia_category_reference , date_added , question_count ,
filename , created FROM (

SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_refer ence` ,
`verse_of_the_day_Bible_trivia`.`date_added` , COUNT(
`verse_of_the_day_Bible_trivia`.`reference` ) AS question_count,
`verse_of_the_day_bible_trivia_ready_made_handouts`.`filenam e` ,
`verse_of_the_day_bible_trivia_ready_made_handouts`.`created `

FROM (

SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE
`verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY
`verse_of_the_day_Bible_trivia`.`date_added` DESC

) AS verse_of_the_day_Bible_trivia

INNER JOIN `Bible_trivia_category` ON `Bible_trivia_category`.`reference` =
`verse_of_the_day_Bible_trivia`.`Bible_trivia_category_refer ence`
LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON
`Bible_trivia_category`.`reference` =
`verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_t rivia_category_reference`

GROUP BY `Bible_trivia_category`.`reference`
HAVING question_count >= 10
ORDER BY `verse_of_the_day_Bible_trivia`.`date_added` DESC )

AS bible_trivia_handouts

WHERE date_added > created

ORDER BY Bible_trivia_category_reference ASC



The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php