CODEWITHSUNDEEP
sql-servert-sql
nav100
nav100

Reputation: 3133

Using order by clause with UNION issue

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

Upvotes: 2

Views: 97

Answers (3)

d_luffy_de
d_luffy_de

Reputation: 967

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

Upvotes: 0

John Bell
John Bell

Reputation: 2350

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

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

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

Upvotes: 1

Related Questions