Sort Distinct Records (please help)

trying to sort records that are in a group by (or distinct)

[techtheatre]

I posted this on April 27 but nobody has responded. Any help?!?

I have a table with numerous fields (probably about 10). Three of them happen to be PageName, PageFamily, and Timestamp. The first two (PageName and PageFamily) are verchar(50) and the field "Timestamp" is a timestamp(14). The primary key is "StoryId". Every time a value is updated, a new row is added to the database (with a unique StoryId number). The timestamp is current and the PageName/Family combination names are often repeated.
I am trying to write a script to pull the page info from my database to make a Google Sitemap (XML) file. I have been successful thus far pulling a list of distinct PageName+PageFamily combinations with this query:

SELECT DISTINCT PageName, PageFamily FROM smp_PageData ORDER BY PageFamily ASC, PageName ASC;

This does exactly what I wanted. It will list only once each Family and Name combination that is unique.

Now I need to add to my output a formatted date from the Timestamp field. After much research online, I figured out that rather than using "DISTINCT" I could achieve what I needed with "GROUP BY" and ended up with the query below:

SELECT DATE_FORMAT(Timestamp,'%Y-%m-%d') AS FormattedTimestamp, PageName, PageFamily FROM smp_PageData GROUP BY PageName, PageFamily;

I thought I had finally gotten it all worked out, but then noticed that the dates shown were all old. It is not showing me the most recent (highest date) for these unique pairs of PageName and PageFamily. I tried all sorts of other things (nested selects, Sort By, etc) and have come up completely empty handed. Anyone know how to fix this query?

Thanks!

[physics]

This is a complex question, sorry it hasn't gotten any answers!

I assume you tried adding an 'ORDER BY Timestamp DESC' at the end of that query?

Check out this thread:
[webmasterworld.com...]

Are you adding new rows so that you can keep a record of the old values? If that's the case maybe another option is to move the old rows into another table when a new row is added so you have a history - this problem would be moot then.

p.s.
This may not help you right away but one thing to consider is to normalize your database better. For example, you said

Every time a value is updated, a new row is added to the database (with a unique StoryId number). The timestamp is current and the PageName/Family combination names are often repeated.

This is a sign of bad normalization which, among other things, can make it more difficult to do some kinds of queries. Maybe normalizing the database more would help (not sure if that's an option though).

[techtheatre]

Adding the ORDER BY clause does not work, because that sorts is AFTER it has already pulled the records. I did read over the link you provided, and while it was not the exact same as what i was trying to do, it helped me figure out the following solution:

SELECT DATE_FORMAT(MAX(Timestamp),'%Y-%m-%d') AS FormattedTimestamp, PageName, PageFamily FROM smp_PageData GROUP BY PageName, PageFamily;

So far this appears to be working. I have not thoroughly checked it out, but it looks good.

THANKS!