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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.