TRICKY - How to SELECT TOP X records when using UNION?

Garth

I have the following query: (SELECT P_Title AS Title FROM tblProjects WHERE Status='True') UNION ALL (SELECT Title FROM tblProjectDocs WHERE Status='True') UNION ALL (SELECT Title FROM tblLinks WHERE Status='True') ORDER BY Title DESC This returns roughly 26 or so records. I want to include only the top 6 or so records. Using SELECT TOP 3...etc does not work correctly - as for some reason, this means records are displayed in an odd order, and more than 6 records are returned in total. I have tried mucking about with the position of brackets, doesn't seem to work. Elsewhere it is suggested that a sub-query might work (e.g. SELECT TOP 6 FROM (query with UNION) ORDER BY Field). This doesn't work either. Any ideas?

Rein Hillmann

IMO you could do this using one of the following methods: 1) Use a temp table and then select top 6 from the temp table 2) If you use this often, create a view and then select top 6 from the view. There might be other ways to do this but the problem is that the ordering is done after all the rows have been UNIONed.

Richard Deeming

Try this: (tested on SQL 2000)

SELECT TOP 6
Title
FROM
(

SELECT
    P\_Title As Title
FROM
    tblProjects
WHERE
    Status='True'

UNION ALL

SELECT
    Title
FROM
    tblProjectDocs
WHERE
    Status='True'

) As Titles

ORDER BY
Title DESC

If you miss out the "As Titles" before the "ORDER BY", you'll get an "Incorrect syntax" error message.