Does anyone out there know how the UNION statement is supposed to work with the TOP
statement in MS-SQL? I'm trying to limit the amount of records that are coming back
to me, and it works great with a cf attribute of maxrows=''n", but I'd rather have SQL
churn it out for me, but it seams to ignore the ORDER BY clause until after the
union...
This query works (but is going to add overhead when the db starts filling up):
<cfquery name="getLatestNews" maxrows="5">
SELECT PressRelease_id AS Record_id, DatePosted, DateWritten, Title, NULL AS
Publication, 1 AS IsPressRelease, 0 AS IsArticle
FROM PressReleases
WHERE IsFeatured != 1
UNION ALL
SELECT Article_id AS Record_id, DatePosted, DateWritten, Title, Publication, 0
AS IsPressRelease, 1 AS IsArticle
FROM Articles
WHERE IsFeatured != 1
ORDER BY DatePosted DESC
</cfquery>
This one only returns 3 records (and they are not ordered by DatePosted like I want,
wah-wah)
<cfquery name="getLatestNewsToo">
SELECT TOP 3 PressRelease_id AS Record_id, DatePosted, DateWritten, Title,
NULL AS Publication, 1 AS IsPressRelease, 0 AS IsArticle
FROM PressReleases
WHERE IsFeatured != 1
UNION ALL
SELECT TOP 3 Article_id AS Record_id, DatePosted, DateWritten, Title,
Publication, 0 AS IsPressRelease, 1 AS IsArticle
FROM Articles
WHERE IsFeatured != 1
ORDER BY DatePosted DESC
</cfquery>
Now, I'm thinking I can add a subquery into each select statement that will grab the
latest three, but I think I might feel a little dirty if I do it that way....any ideas
out there?
Tyler Silcox
email | [EMAIL PROTECTED]
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
