[thelist] Tricky Transact SQL Query

[Paul Cowan]

Hi Jacques,

There are a couple of ways you can do this, but I'll just give you one, 
because I'm not near a SQL server machine and the second way is a shade 
tricky. What you can do is something like this, with a subquery:

	SELECT
		Publication.pubID,
		Publication.publication,
		Issue.IssueID,
		Issue.Issue,
		Issue.ReleaseDate
	FROM
		Publication
	INNER JOIN Issue ON
		(Publication.pubID = Issue.pubFK)
	WHERE
		(Issue.ReleaseDate IN (SELECT TOP 5 ReleaseDate FROM
		Issue WHERE Issue.pubFK = Publication.pubID))
	ORDER BY
		publication.publication,
		issue.releasedate desc
	

The subquery here just says 'give me the 5 latest dates for each 
publication' -- and this is then used to filter the results.

The problem with this? It will fail (returning more or less than 5 rows for 
each publication) if there are multiple issues of the same publication on 
the same date. I don't know if this ever happens -- if it doesn't, this may 
be a good solution. You might want to look at putting a unique constraint 
on (pubFK, ReleaseDate) to ensure this -- depending on your needs, having 
an enforced restriction on inserts may save problems later.

Cheers,

Paul