[thelist] Tricky Transact SQL Query

[Jacques Capesius]

Hi Evolters,

I have two tables sitting in an MSSQL Server.

PUBLICATION
-------------------------
pubID |	Publication |
-------------------------
1  	|	Updates     |
2  	|	Events      |
-------------------------

ISSUE
-------------------------------------------------------
issueID | pubFK | Issue 		   | Release Date |
-------------------------------------------------------
1	  | 1     | Corp Policy Update   | 10-Mar-2004  |
2	  | 1     | New Product Codes    | 1-Apr-2004   |
3       | 1     | Cheat Codes          | 4-Dec-2003   |
4       | 1     | Bubba Ho-Tep         | 25-May-2004  |
5       | 1     | Spoon!               | 3-Sep-2002   |
6       | 1     | Webmaster smash      | 1-May-2001   |
7       | 2     | MiniCON              | 14-Apr-2000  |
8       | 2     | MaxiCON              | 15-Apr-2000  |
9       | 2     | MiniKHAAAN!          | 15-Apr-2001  |
-------------------------------------------------------

I'm trying to build a TransactSQL query that lists the top 5 most recent
issues of each publication, grouped by publication, ordered within each
publication by date descending. In short, I'm looking for a result set
like this:

pubID | Publication | issueID | Issue			| Release Date
---------------------------------------------------------------------
2	| Events	  | 9		| MiniKHAAAN!		|
15-Apr-2001
2	| Events	  | 8       | MaxiCON			|
15-Apr-2000
2	| Events	  | 7       | MiniCON			|
14-Apr-2000
1	| Updates	  | 4		| Bubba Ho-Tep	     	|
25-May-2004
1	| Updates	  | 2       | New Product Codes	| 1-Apr-2004
1	| Updates	  | 1		| Corp Policy Update	|
10-Mar-2004
1	| Updates	  | 3  	| Cheat Codes		| 4-Dec-2003
1	| Updates	  | 5	      | Spoon!		      |
3-Sep-2002

Do any of you Transact SQL Wizards know of a single query that can
produce this kinda result set? Any pointers / clues would be most
appreciated.

TIA and thanks for reading.

-jacques :)