How do I put a ORDER BY in a UNION ALL query that is generated by a dynamic SQL statement

I get a "The order by clause is invalid in the views, inline functions,.." etc. error.

I need to order by the total points. Without the "order by" the query works just fine and returns exactly what I need but without the correct order.

SELECT 	ageGroupID,
		classID,
		[1],[2]
		,total
 FROM 
 (
 SELECT rp.raceID,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3
 and rp.classid = 1 
 and rp.agegroupid = 1 
	order by total	
) p
pivot 
(
sum(points) 
for raceid
 in([1],[2])) 
 as total
 
    --Puts space between unions
   UNION ALL 
   SELECT '','',NULL,NULL,0 
   UNION ALL 

SELECT  ageGroupID,
		classID,
		[1],[2]
		,total
 FROM 
 (
 SELECT rp.raceID,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3
 and rp.classid = 2 
 and rp.agegroupid = 1 
	order by total	
) p
pivot 
(
sum(points) 
for raceid
 in([1],[2]) )
 as total

Open in new window

falco78Asked:
Who is Participating?
 
cyberkiwiCommented:
DECLARE 
@RC int,
@CATEGORY int,
@AGEGROUP int,
@CLASS int

DECLARE @sql as nvarchar(MAX) = ''


DECLARE @tblRaceNames as TABLE(y varchar(20) NOT NULL PRIMARY KEY)
INSERT INTO @tblRaceNames 
	SELECT raceid FROM Web.tblRaces where YEAR(raceDate) = 2010 order by racedate

--
DECLARE @cols as nvarchar(MAX),
        @raceID as int,
		@racename as varchar(50)
		
SET @raceID = (Select MIN(y) FROM @tblRaceNames)		
SET @racename = (Select MIN(y) FROM @tblRaceNames)
SET @cols = N''

WHILE @racename IS NOT NULL
BEGIN
	SET @cols = @cols + N',['+CAST(@racename as nvarchar(10))+N']'
	SET @racename = (SELECT MIN(y) FROM @tblRaceNames WHERE y > @racename)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))



SET @AGEGROUP = 1
WHILE @AGEGROUP < 12
    BEGIN

		SET @CLASS = 1
			WHILE @CLASS < 7 
				 BEGIN 


IF @sql <> ''
   SET @sql = @sql + ' UNION ALL SELECT '''','''','''',' + CAST(@agegroup as nvarchar) + ',' +
   CAST(@CLASS-0.5 as nvarchar) + ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 0 UNION ALL '    
      
SET @sql = @sql + N'
SELECT  Name,
	    className,
		categoryName,
		ageGroupID,
		classID,
		[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
		,total
 FROM 
 (
 SELECT r.RiderFirstName + '' '' + r.RiderLastName as Name,
		rp.raceID,
		class.className,
		c.categoryName,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
	INNER JOIN [dbAMBCS].[Web].[tblRiders] r
	on r.RiderID = rp.riderID
	INNER JOIN [dbAMBCS].[Web].[tblCategories] c
	on c.categoryId = rp.categoryID
	INNER JOIN [dbAMBCS].[Web].[tblClass] class
	on class.classId = rp.classID
	INNER JOIN [dbAMBCS].[Web].[tblAgeGroup] ag
	on ag.AgeGroupID = rp.AgeGroupID
 WHERE rp.categoryid = 3
 and rp.classid = ' + CAST(@CLASS as nvarchar) + ' 
 and rp.agegroupid = ' + CAST(@agegroup as nvarchar) + ' 
		
) p
pivot 
(
sum(points) 
for raceid
 in(' + @cols + ')) 
 as total
 --order by total
 '
 
  SET @CLASS +=1
 	END
 	
	SET @AGEGROUP +=1
		END	
		

SET @sql = 'select * from (' + @sql + ') X order by agegroupID, classID, total desc'
		 		
 print @sql
 
if @sql <> ''
 EXEC sp_executesql @sql

Open in new window

0
 
cyberkiwiCommented:
SELECT 	ageGroupID,
		classID,
		[1],[2]
		,total
 FROM 
 (
 SELECT rp.raceID,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3
 and rp.classid = 1 
 and rp.agegroupid = 1 
) p
pivot 
(
sum(points) 
for raceid
 in([1],[2])) 
 as total
 
    --Puts space between unions
   UNION ALL 
   SELECT '','',NULL,NULL,0 
   UNION ALL 

SELECT  ageGroupID,
		classID,
		[1],[2]
		,total
 FROM 
 (
 SELECT rp.raceID,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3
 and rp.classid = 2 
 and rp.agegroupid = 1 
) p
pivot 
(
sum(points) 
for raceid
 in([1],[2]) )
 as total
order by total	

Open in new window

0
 
cyberkiwiCommented:
This should be all you need

SELECT  ageGroupID, NULLIF(classID,1.5) classID, [1],[2] ,total
 FROM
 (
      SELECT rp.raceID, rp.classID, rp.agegroupID, rp.points,
            SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3 and rp.classid in (1,2)  and rp.agegroupid = 1) p
pivot
(sum(points) for raceid in([1],[2]) ) as total
UNION ALL     SELECT null, 1.5, NULL,NULL,0
order by classID, total      

Your original query shouldn't have worked, not unless ageGroupID and classID are varchar, otherwise they won't work when UNION-ed with the other two parts where the first two columns are INT.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
cyberkiwiCommented:
Correction to previous query

SELECT  ageGroupID, classID, [1],[2] ,total
 FROM 
 (
	SELECT rp.raceID, rp.classID, rp.agegroupID, rp.points,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3 and rp.classid in (1,2)  and rp.agegroupid = 1) p
pivot 
(sum(points) for raceid in([1],[2]) ) as total
UNION ALL     SELECT null, null, NULL,NULL,0
order by isnull(classID, 1.5), total	

Open in new window

0
 
cyberkiwiCommented:
Interestingly,

select 1 a, 2 b
union all
select '', ''

returns

a           b
----------- -----------
1           2
0           0

So my apologies about the 2nd comment above.  Revised query to give you 0,0 for the "spacer" row

SELECT  ageGroupID, classID, [1],[2] ,total
 FROM 
 (
	SELECT rp.raceID, rp.classID, rp.agegroupID, rp.points,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3 and rp.classid in (1,2)  and rp.agegroupid = 1) p
pivot 
(sum(points) for raceid in([1],[2]) ) as total

UNION ALL SELECT 0, 0, NULL,NULL,0

order by isnull(classID, 1.5), total	

Open in new window

0
 
falco78Author Commented:
I have multiple UNION ALL and I need the "TOTAL" ordered for each group seperated by a blank row. I don't want the entire result set ordered together. The above works but orders everything group by total.

so for example:

someID        1        2        Total
11                 0      40         40
16                 8      20         28
33                 10    10         20
 4                  8       4         12
-                   -        -           -
22               23        24        47
12               21       15         36
 8                18        11        29
7                 10         4          14

NOT

someID        1        2        Total
22               23        24        47
11                 0       40         40
12               21       15         36
 8                18        11        29
-                   -        -           -
16                 8       20         28
33                10     10         20
7                 10        4          14
 4                  8        4          12
0
 
cyberkiwiCommented:
Did you actually try it?

The two blocks are separated by classID, and within a classID, it is sorted by Total desc.  Between the two classIDs is a blank line.

If you have anonymised your query, then you have not transliterated the sample given properly to your real table/columns.
0
 
cyberkiwiCommented:
It may be missing a level of subquery, so here's a correction

select ageGroupID, classID, [1],[2] ,total
FROM
(
SELECT  ageGroupID, classID, [1],[2] ,total
FROM
 (
      SELECT rp.raceID, rp.classID, rp.agegroupID, rp.points,
            SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
 WHERE rp.categoryid = 3 and rp.classid in (1,2)  and rp.agegroupid = 1) p
pivot
(sum(points) for raceid in([1],[2]) ) as total

UNION ALL SELECT 0, 0, NULL,NULL,0
) X
order by isnull(classID, 1.5), total
0
 
falco78Author Commented:
Okay, sorry. Let me include the actual code for the query. This code work perfectly and the result set is exactly what I am looking for minus the ORDER by total for each group.

Here is an screenshot from management studio showing part of the result set.  I want the total to be ordered asc

 result set that needs to be orderd by group
DECLARE 
@RC int,
@CATEGORY int,
@AGEGROUP int,
@CLASS int

DECLARE @sql as nvarchar(MAX) = ''


DECLARE @tblRaceNames as TABLE(y varchar(20) NOT NULL PRIMARY KEY)
INSERT INTO @tblRaceNames 
	SELECT raceid FROM Web.tblRaces where YEAR(raceDate) = 2010 order by racedate

--
DECLARE @cols as nvarchar(MAX),
        @raceID as int,
		@racename as varchar(50)
		
SET @raceID = (Select MIN(y) FROM @tblRaceNames)		
SET @racename = (Select MIN(y) FROM @tblRaceNames)
SET @cols = N''

WHILE @racename IS NOT NULL
BEGIN
	SET @cols = @cols + N',['+CAST(@racename as nvarchar(10))+N']'
	SET @racename = (SELECT MIN(y) FROM @tblRaceNames WHERE y > @racename)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))



SET @AGEGROUP = 1
WHILE @AGEGROUP < 12
    BEGIN

		SET @CLASS = 1
			WHILE @CLASS < 7 
				 BEGIN 


IF @sql <> ''
   SET @sql = @sql + ' UNION ALL SELECT '''','''','''','''','''',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 0 UNION ALL ' 
      
      
SET @sql = @sql + N'
SELECT  Name,
	    className,
		categoryName,
		ageGroupID,
		classID,
		[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
		,total
 FROM 
 (
 SELECT r.RiderFirstName + '' '' + r.RiderLastName as Name,
		rp.raceID,
		class.className,
		c.categoryName,
		rp.classID,
		rp.agegroupID,
		rp.points,
		rp.riderid,
		SUM(points) OVER (PARTITION by rp.riderID) as total
 FROM [dbAMBCS].[Web].[tblRiderPoints] rp
	INNER JOIN [dbAMBCS].[Web].[tblRiders] r
	on r.RiderID = rp.riderID
	INNER JOIN [dbAMBCS].[Web].[tblCategories] c
	on c.categoryId = rp.categoryID
	INNER JOIN [dbAMBCS].[Web].[tblClass] class
	on class.classId = rp.classID
	INNER JOIN [dbAMBCS].[Web].[tblAgeGroup] ag
	on ag.AgeGroupID = rp.AgeGroupID
 WHERE rp.categoryid = 3
 and rp.classid = ' + CAST(@CLASS as nvarchar) + ' 
 and rp.agegroupid = ' + CAST(@agegroup as nvarchar) + ' 
		
) p
pivot 
(
sum(points) 
for raceid
 in(' + @cols + ')) 
 as total
 --order by total
 '
 
  SET @CLASS +=1
 	END
 	
	SET @AGEGROUP +=1
		END	
		

		 
		 
		
 print @sql
 
if @sql <> ''
 EXEC sp_executesql @sql

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.