[RESOLVED] Join, then sort by one table and display the other

Krik

Well that doesn't discribe it very well but hopefully with a bit more room I can explain it.

I am not sure how best to discribe it. This is similar to a blog but not quite, may be even more closely a forums.

What I have is documents that are quite lengthy and that are constantly updated or added to. And the individual parts of each document are stored in a separtate table (Document_Parts) and they reference back to another table that has the Title and information related to the title. Each entry in the Document_Parts table has the date it was entered or updated.

What I need to beable to do is sort the Titles by the most recent date it had an entry made in the Document_Parts table.

So I have tried a variety of differrent JOIN's with ORDER BY. Now the ORDER BY is working fine. The problem is, I get all the results of the Document_Parts table back and it is displaying a Tilte for each one.

What I need is for the query to find the more recent date for each title and then return each title, only once, with the titles ordered by the date of the most recent entry made to it.

So if I have a Title table that looks like such

[CODE]

| tc1 | tc2 |

| 1 | a |

| 2 | b |

[/CODE]
And a Document_Parts table like so

[CODE]

| dc1 | dc2 | dc3 | dc4 |

| 1 | x | 2 | 2008-11-01 |

| 2 | y | 1 | 2008-11-02 |

| 3 | z | 2 | 2008-11-03 |

[/CODE]
With a join of dc3 to tc1 the results that are output needs to be

[CODE]

| tc2 | dc4 |

| b | 2008-11-03 |

| a | 2008-11-02 |

[/CODE]

Obviously my tables have more columns than that but those demostrate it as simply as possible.

I suspect this is much more complex query than anything I have working with as of yet.

Krik

Been reading alot in the MySQL manual and forums and have come up with

SELECT Titles.Title, dp.Date
FROM `Titles`
LEFT JOIN Documant_Parts dp ON dp.Title_num = Titles.id
GROUP BY Titles.Title
HAVING COUNT(Titles.Title) >= 1
ORDER BY Titles.Priority DESC, dp.Date DESC

The GROUP BY and HAVING COUNT seem to get me down to just the titles and the dates but it is giving the oldest date with them.

Any idea on how to get he newest date?

Krik

It's is good to know smart people.

Finally got a hold of a friend of mine hes an SQL wizard and this is what we got

SELECT *
FROM `Titles`
LEFT JOIN (SELECT DP2.Title, SQ2.Date, SP2.Account
FROM Document_Parts DP2
INNER JOIN (SELECT Title, MAX(Date) AS Date
FROM Document_Parts
GROUP BY Title) 
SQ2 ON DP2.Title = SQ2.Title
GROUP BY Title)
DP1 ON DP1.Title = Titles.id
WHERE `Category` = '$cat'
ORDER BY Titles.Priority DESC, DP1.Date DESC

Of course alot more going on there than I originally asked about but it may help someone else.