Multi-way Sort: Can it be done?

[Davide Marney]

I have been scratching my head over how to accomplish the following sort. Here is an amusing puzzle for you SQL wizards out there:

1. I have a list of articles which are (generally) sequential parts of a series. Each article record captures a Series Name and a Publication Date. Not every article will be part of a Series, but all articles will have a date.

2. The articles should be grouped first by Series, with the most recently-published Series listed first. Any article that is not part of a Series should follow after the closest Series, in date order.

3. Within each Series grouping, the earliest-published article should be listed first.

-----------------

A simple example:

1/1 Series A
1/2 (No Series)
1/3 Series A

The order we want is:

Group: Series A
1/1
1/3

Group: (None)
1/2

------------

A bit more complicated:

1/1 Series A
1/2 Series A
1/3 (No Series)
1/4 Series A
1/5 Series B
1/6 (No Series)

Should be sorted as:

Group: (none)
1/6

Group: Series B
1/5

Group: Series A
1/1
1/2
1/4

Group: (none)
1/3

Any thoughts/ideas would be most appreciated.