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

You can't place an `ORDER BY` before a `UNION` it has to go at the very end. You'll need to use subqueries. — Thom A, Nov 25 '20 at 19:02
@Larnu - after adjusting ORDER BY, I remain with a different error. I am sure your not able to see my results, however, this is the other error: Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'Select'. Msg 1062, Level 15, State 1, Line 32 The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause. — Sweetcorn, Nov 25 '20 at 19:21

Thom A · Answer 1 · 2020-11-25T19:33:58.460

0

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;

edited Nov 25 '20 at 19:33

answered Nov 25 '20 at 19:25

Thom A

88,727
11
45
75

Is it possible to apply DISTINCT 'Server2' AS 'Server', and TOP 1 on a single column ? – Sweetcorn Nov 25 '20 at 19:49
You don't apply `DISTINCT` to a column, you apply it to the dataset, @hmm4089 . With the `TOP (1) WITH TIES` it made little sense being in there as you should already be getting distinct results; if you weren't the `OVER` clause for your `RANK` was wrong. – Thom A Nov 25 '20 at 19:58
I added 'Server1' AS 'Server' in top query and I only get 1 rows displaying 'Server', my sample data does not list. – Sweetcorn Nov 25 '20 at 20:08
If you add a column `server` to the top subquery, you'll need to uncomment the column in the bottom one, @hmm4089 – Thom A Nov 25 '20 at 20:09
@Lurna - Will review your example. – Sweetcorn Nov 25 '20 at 20:12
larnu* @hmm4089 . – Thom A Nov 25 '20 at 20:14
@Lurna - Query matches with servers, however it is not retrieving all lines for the in.Name, in.Vendor for the COUNT(*) Count_InvoiceNo. – Sweetcorn Nov 25 '20 at 20:19
- Query matches with servers, however it is not retrieving all lines for the in.Name, in.Vendor for the COUNT(*) Count_InvoiceNo. – Sweetcorn Nov 25 '20 at 20:28
Probably because of the `TOP` filtering them, "mhm4098".... You'll need to provide a sample data and expected results at this stage. – Thom A Nov 25 '20 at 20:31
where do I provide sample data? I am not able to edit from my side. – Sweetcorn Nov 25 '20 at 20:34
You can always [edit](https://stackoverflow.com/posts/65010856/edit) your own post unless it has been locked. – Thom A Nov 25 '20 at 20:34
@Lurna - I have edited and updated query and included image with needed line data listing rows. – Sweetcorn Nov 25 '20 at 20:55

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

1 Answers1