I have a union on the same table [MyTable]
so I can select certain values as the top 5, the logic of which I’m excluding here to simplify the question (I hope):
The first table I alias as tbl1
– can I reference this alias somehow after the UNION statement so that I can exclude the results from it?
I tried like so but it doesn’t recognise tbl1
SELECT top 5 tbl1.Id, tbl1.Description, 'first' FROM ( -- query [MyTable] joined with others to get particular result set ) as tbl1 UNION SELECT tbl2.Id, tbl2.Description, 'second' FROM [MyTable] as tbl2 WHERE tbl2.Id NOT IN (SELECT Id FROM tbl1)
Or do I just have to redo the first query in the 2nd half in order to get those Id’s to exclude?
I’m using SQL Server 2012 for this.
Advertisement
Answer
Use a CTE
:
WITH cte AS ( SELECT top 5 tbl1.Id, tbl1.Description, 'first' FROM ( -- query [MyTable] joined with others to get particular result set ) as tbl1 ) SELECT * FROM cte UNION ALL SELECT tbl2.Id, tbl2.Description, 'second' FROM [MyTable] as tbl2 WHERE tbl2.Id NOT IN (SELECT Id FROM cte)