Displaying results in date order

Anon

Okay, this is kind of complicated. I have a table with the following fields:

storyid
parentstoryid
[other stuff]
date

When a new story is added, it's parentstoryid and storyid are the same. If a new chapter is added to that story, the chapter's parentstoryid is the same number as the first chapter's. Now, here's my problem: I want to display some summary results from the first chapter's row, but based on the most recent chapter's date. To explain it visually:

Table info:

Story 1, Chapter 1:
storyid = 1
parentstoryid = 1
date = 05/01/02

Story 2, Chapter 1:
storyid = 2
parentstoryid = 2
date = 05/03/02

Story 1, Chapter 2:
storyid = 3
parentstoryid = 1
date = 05/05/02

[Other stories continuing on]

The results would display Story 1, Chapter 1's info, but it would be displayed before Story 2 because Story 1 had a Chapter 2 that was more recent than Story 2's. Make sense? How in the world do I do this SQL query? Is it even possible?

Anon

Use the ORDER BY feature of MySQL...

"SELECT storyid, parentstoryid, date FROM table ORDER BY date ASC"

Anon

Did you read my question carefully? How do I select the date from one row, but display the results from another row (the only tie between the two rows being parentstoryid)?

Anon

Umm...

SELECT DISTINCT
t1.otherstuff
FROM
storytable t1, storytable t2
WHERE
t1.storyid=t2.parentstoryid
ORDER BY
t2.date DESC

I think. That's untested. But I do get the feeling it's summat along those lines.

It pairs each chapter off against its parent. The parent's otherstuff is held on to for returning. Note that the parent's otherstuff will be listed multiple times at this point - once for each chapter in the story.

The pairs all get sorted in descending date order of each story Chapter.

DISTINCT (should, I reckon) drop all but the first copy of each of the otherstuffs. Because of the ordering, the first one would be the otherstuff for the parent of the most recent chapter. The second should then be the otherstuff for the parent of the second most recent chapter.