UNION in SQL SERVER query

Question

I am beginner in SQL SERVER. I have this 2 query:

1st query:

SELECT 'OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR ORDER BY DAJ_TKHDE DESC )='OK') D

2nd query:

SELECT 'NOT OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR ORDER BY DAJ_TKHDE DESC )='NOT OK') D

When I try to run this two query, the result show up perfectly but when I try to combine this two query using UNION like this:

SELECT 'OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR ORDER BY DAJ_TKHDE DESC )='OK') D
UNION
SELECT 'NOT OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR ORDER BY DAJ_TKHDE DESC )='NOT OK') D

This error message show up:

Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Can anyone show me how to fix this error?

OK. I already post the full query after union. Check my question above. — crystal, Mar 18 '14 at 02:00

Jian Huang · Answer 1 · 2014-03-18T02:23:02.273

0

The error message already points out the answer. Simply delete both 'ORDER BY DAJ_TKHDE DESC' in queries. That is it! Then run the union query again.

SELECT 'OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR)='OK') D

UNION

SELECT 'NOT OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND (SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE     
DAJ_NODAFTAR=D.DAJ_NODAFTAR)='NOT OK') D

update

To provide a second solution (alternative method),

declare @MyTable table
(
    Status varchar(25),
    Count int
)

insert into @MyTable
your first query

insert into @MyTable
your second query

select * from @MyTable

edited Mar 18 '14 at 02:23

answered Mar 18 '14 at 01:49

Jian Huang

1,155
7
17

1

That changes the logic of the inner queries. – Szymon Mar 18 '14 at 01:53
@Szymon You are correct. Another way is to declare table variable, and load the content of 1st and 2nd queries into the table. However, that is not the 'union' solution user looking for. – Jian Huang Mar 18 '14 at 02:02
It is not as simple as you think. If I delete both `ORDER BY DAJ_TKHDE DESC`, YES the result show up without error message but the result is wrong. It is not count correctly. I use this `(SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE DAJ_NODAFTAR=D.DAJ_NODAFTAR)` to make sure when it do counting, i would not include duplicate data for same `DAJ_NODAFTAR`. – crystal Mar 18 '14 at 02:07
@user3106393 My mistake. However, to solve your problem, could you use other alternative methods such as using table variables? Or you must use UNION? – Jian Huang Mar 18 '14 at 02:14
@user3106393 I added it to the content of my post. Let me know you have any question. – Jian Huang Mar 18 '14 at 02:25

score 0 · Answer 2 · edited May 23 '17 at 11:52

You should be able to use a windowed CTE first to eliminate the need for the order in the subqueries. The fields complexity is such that I haven't built sample data to verify, but something like:

WITH cte AS (
 SELECT DAJ_KLASIFIKASI
       ,DAJ_NODAFTAR
       ,ROW_NUMBER() OVER (PARTITION BY DAJ_NODAFTAR ORDER BY DAJ_TKHDE DESC)
   FROM BATM112_RKDKLASIFIKASI
)
SELECT 'OK' AS STATUS,
       COUNT(*) AS BIL
FROM   (SELECT D.DAJ_NODAFTAR
        FROM   BATM112_DES_AMJENTERA AS D
        WHERE  D.DAJ_KODKAT = 'VTC'
               AND (SELECT DAJ_KLASIFIKASI FROM cte WHERE DAJ_NODAFTAR = D.DAJ_NODAFTAR AND rn=1) = 'OK') AS D
UNION
SELECT 'NOT OK' AS STATUS,
       COUNT(*) AS BIL
FROM   (SELECT D.DAJ_NODAFTAR
        FROM   BATM112_DES_AMJENTERA AS D
        WHERE  D.DAJ_KODKAT = 'VTC'
               AND (SELECT DAJ_KLASIFIKASI FROM cte WHERE DAJ_NODAFTAR = D.DAJ_NODAFTAR AND rn=1) = 'NOT OK') AS D

There is a similar discussion over at sql-server-order-by-in-subquery-with-union.

I'm using SQL SERVER 2000. This style of query maybe not recognized in this version because when I try to run, this error message show up: `Incorrect syntax near the keyword 'WITH'. 'ROW_NUMBER' is not a recognized function name.` — crystal, Mar 18 '14 at 02:29
Oh, yeah, this totally won't work with 2000. In the future it helps everyone if you tag with the appropriate version. — Karl Kieninger, Mar 18 '14 at 03:16

cyan · Answer 3 · 2014-03-18T02:42:37.330

you will need to rearrange the queries. my aim with below queries are to keep the subquery value (the query that has the "order by" in a table variable) in a table variable and the table variable will replace your subquery with out "Order by"

Declare @temp Table
(
 DAJ_KLASIFIKASI nvarchar(50)
)

Insert into @temp values
SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE   
DAJ_NODAFTAR in ( SELECT D.DAJ_NODAFTAR FROM BATM112_DES_AMJENTERA AS D WHERE D.DAJ_KODKAT='VTC') and DAJ_Klasifikasi = 'NOT OK' ORDER BY DAJ_TKHDE DESC 

Insert into @temp values
SELECT TOP 1 DAJ_KLASIFIKASI FROM BATM112_RKDKLASIFIKASI WHERE   
DAJ_NODAFTAR in (SELECT D.DAJ_NODAFTAR FROM BATM112_DES_AMJENTERA AS D WHERE D.DAJ_KODKAT='VTC') and DAJ_Klasifikasi = 'OK' ORDER BY DAJ_TKHDE DESC

YOur modified query:

SELECT 'OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND ((SELECT DAJ_KLASIFIKASI FROM @temp where DAJ_Klasifikasi='OK')='OK') D
UNION
SELECT 'NOT OK' AS STATUS, COUNT(*) AS BIL 
FROM 
(SELECT D.DAJ_NODAFTAR 
FROM BATM112_DES_AMJENTERA AS D 
WHERE D.DAJ_KODKAT='VTC' 
AND ((SELECT DAJ_KLASIFIKASI FROM @temp where DAJ_Klasifikasi='NOT OK')='NOT OK') D

Note: this query is not tested since i do not have the example data. so please test it thoroughly before use it. you need to aim to remove the order by in you main query.

No. BATM is not stand for BATAM. Not related to it – crystal Mar 18 '14 at 03:19 — crystal, Mar 18 '14 at 03:19

score 0 · Answer 4 · answered Mar 18 '14 at 03:37

Is it possible you've over complicated it with an unnecessary union? I have a little trouble following the table/field names, but I think all you trying to do is bubble up an OK or NOT OK and count the occurrences. Maybe something with a GROUP BY instead?

SELECT STATUS,
       COUNT(*) AS BIL
  FROM (SELECT D.DAJ_NODAFTAR
              ,(SELECT TOP 1 
                       DAJ_KLASIFIKASI
                  FROM BATM112_RKDKLASIFIKASI
                 WHERE DAJ_NODAFTAR = D.DAJ_NODAFTAR
                 ORDER BY DAJ_TKHDE DESC) STATUS
          FROM BATM112_DES_AMJENTERA AS D
         WHERE D.DAJ_KODKAT = 'VTC') a
 GROUP BY STATUS

score 0 · Answer 5 · edited May 23 '17 at 12:23

0

Just remove the order by from 1st select query. When using union, this is not allowed. So, you are getting this error.

When using union, you can use order by clause in the last ( here 2nd ) select query only.

If your requirement really needs order by in the 1st query too, the solution can be found here.

SQL Query - Using Order By in UNION

edited May 23 '17 at 12:23

Community

1
1

answered Dec 04 '15 at 01:56

Sundararaj Govindasamy

8,180
5
44
77

UNION in SQL SERVER query

5 Answers5