Using order by clause with UNION issue

Question

I am getting the following error when I user Order by with UNION.

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

declare @results table (hdate date, name varchar(100), id1 int)

insert into @results select cast('10-01-2015' as date), 'val1 1', 1
insert into @results select cast('10-02-2015' as date), 'val2 2', 2

insert into @results select cast('10-03-2015' as date), 'thanks 1', 3
insert into @results select cast('10-04-2015' as date), 'thanks 2', 3

select  (DATENAME(dw, hdate) + ', ' + 
        DATENAME(mm, hdate) + ' '  + 
      DATENAME(dd, hdate)) AS h_date, name AS h_name from @results where id1 in (1,2) 
order by id1
UNION
SELECT (STUFF((
        SELECT ', ' + (DATENAME(dw, hdate) + ', ' + 
       DATENAME(mm, hdate) + ' '  + 
       DATENAME(dd, hdate))
        FROM @results
        WHERE id1 = 3
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS h_date, 'Giving Day' AS h_name

Stanislovas Kalašnikovas · Accepted Answer · 2015-10-12 12:10:02Z

You can try in following:

declare @results table (hdate date, name varchar(100), id1 int)

insert into @results select cast('10-01-2015' as date), 'val1 1', 1
insert into @results select cast('10-02-2015' as date), 'val2 2', 2

insert into @results select cast('10-03-2015' as date), 'thanks 1', 3
insert into @results select cast('10-04-2015' as date), 'thanks 2', 3

SELECT  id1,
        (DATENAME(dw, hdate) + ', ' + 
        DATENAME(mm, hdate) + ' '  + 
        DATENAME(dd, hdate)) AS h_date, name AS h_name from @results where id1 in (1,2) 

UNION

SELECT DISTINCT id1,(STUFF((
        SELECT ', ' + (DATENAME(dw, hdate) + ', ' + 
               DATENAME(mm, hdate) + ' '  + 
               DATENAME(dd, hdate))
        FROM @results r2
        WHERE  r1.id1 = r2.id1
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS h_date, 'Giving Day' AS h_name
FROM @results r1
WHERE id1 = 3 
ORDER BY id1

John Bell · Accepted Answer · 2015-10-12 12:11:42Z

0

As the error suggests, you can't use an ORDER BY with a UNION. So you have to wrap it up in a sub-query before ordering it:

select h_date, h_name from (
select  (DATENAME(dw, hdate) + ', ' + 
        DATENAME(mm, hdate) + ' '  + 
      DATENAME(dd, hdate)) AS h_date, name AS h_name 
      , id1
      from @results 
      where id1 in (1,2) 
UNION
SELECT (STUFF((
        SELECT ', ' + (DATENAME(dw, hdate) + ', ' + 
       DATENAME(mm, hdate) + ' '  + 
       DATENAME(dd, hdate))
        FROM @results
        WHERE id1 = 3
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS h_date, 'Giving Day' AS h_name, 3 as id1) q
order by id1

answered Oct 12, 2015 at 12:11

John Bell

2,3501 gold badge15 silver badges23 bronze badges

Comments

d_luffy_de · Accepted Answer · 2015-10-12 12:15:23Z

This works too:

declare @results table (hdate date, name varchar(100), id1 int)

insert into @results select cast('10-01-2015' as date), 'val1 1', 1
insert into @results select cast('10-02-2015' as date), 'val2 2', 2

insert into @results select cast('10-03-2015' as date), 'thanks 1', 3
insert into @results select cast('10-04-2015' as date), 'thanks 2', 3

select * FROM ( SELECT TOP 100 PERCENT (DATENAME(dw, hdate) + ', ' + 
        DATENAME(mm, hdate) + ' '  + 
      DATENAME(dd, hdate)) AS h_date, name AS h_name from @results where id1 in (1,2) 
ORDER BY id1)A
UNION 
select * FROM ( SELECT TOP 100 PERCENT (STUFF((
        SELECT ', ' + (DATENAME(dw, hdate) + ', ' + 
       DATENAME(mm, hdate) + ' '  + 
       DATENAME(dd, hdate))
        FROM @results
        WHERE id1 = 3
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS h_date, 'Giving Day' AS h_name
    ) AS B

Collectives™ on Stack Overflow

Using order by clause with UNION issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related