Tweaking incorrect syntax near the keyword 'UNION' and 'Top'

Question

I very much appreciate your help on this one, would really like for someone to tell me why Select Distinct and UNION ALL errors and it appears that 'TOP' (1) also has some union issue with clause which I am not familiar with even after I have done my due diligence syntax research. Is it possible to link a second server with my query, I am simply trying to see if the second server needs a different format after "Select top (1)".

Looking for this Output:

My error messages:

Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'UNION'.

Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'top'.

Msg 319, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

SQL query:

SELECT DISTINCT
'SERVER1' AS 'Server'
FROM (
Select top (1) with ties in.Name, in.Vendor, count(*) Count_InvoiceNo

FROM Data.dbo.Invoices AS in

where in.InvDate >= DATEADD(MONTH,-12, GETDATE())

group by in.Name, in.Vendor

order by rank() over(partition by in.Name order by count(*) DESC)) sq


UNION ALL



SELECT DISTINCT
'SERVER2' AS 'Server'
FROM (
Select top (1) with ties in.Name, in.Vendor, count(*) Count_InvoiceNo

FROM Data.dbo.Invoices AS in

where in.InvDate >= DATEADD(MONTH,-12, GETDATE())

group by in.Name, in.Vendor

order by rank() over(partition by in.Name order by count(*) DESC)) sq

Answer 1

As I said:

You can't place an ORDER BY before a UNION it has to go at the very end. You'll need to use subqueries

For example:

SELECT *
FROM (
    SELECT TOP (1) WITH TIES
        in.Name, in.Vendor, COUNT(*) Count_InvoiceNo
    FROM 
        Data.dbo.Invoices AS in
    WHERE 
        in.InvDate >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY
        in.Name, in.Vendor
    ORDER BY
        RANK() OVER (PARTITION BY in.Name ORDER BY COUNT(*) DESC))sq
UNION ALL
SELECT *
FROM (
    SELECT TOP (1) WITH TIES 
        --'Server2' AS 'Server' --This column isn't in your top query
         in.Name, in.Vendor, COUNT(*) Count_InvoiceNo
    FROM 
        Analytics.dbo.Invoices AS in
    WHERE 
        in.InvDate >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY
        in.Name, in.Vendor
    ORDER BY
        RANK() OVER (PARTITION BY in.Name ORDER BY COUNT(*) DESC)) sq;