Understanding Order by... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Understanding Order by...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-03-22 : 15:51:53

I need some help understanding the order of how things will appear.

I want 009 that's in the Reg field to be the last one to appear (200 should be in front of 009)


DOC	Region	Area	Dist	Reg
	PHI			<a href=region.aspx?Reg=C>PHI</a>
	PHI	01		<a href=areadistlisting.aspx?reg=C&Area=01>Area 1</a>
C	PHI	01	200	009
PHI				<a href=distdoclisting.aspx?dist=200>200</a>

This stored procedure produces the above table. I believe I don't have the order by set up correctly:
order by doc, area, reg,regionacronym, dist (This is at the bottom of the stored procedure)



SELECT '' AS doc, RegionAcronym, '' as area, '' as dist,'<a href=region.aspx?Reg=' + Reg + '>' + RegionAcronym + '</a>' as reg,
       SUM(totpenfodds)      AS 'totpenfodds', 
       SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
	   SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
FROM   T16Report 
where reg='c'
group by   RegionAcronym,reg

union

select '' as doc,RegionAcronym, Area,'' as Dist,'<a href=areadistlisting.aspx?reg=' + reg + '&Area=' + Area + '>' +'Area ' + right(area,1) + '</a>' as reg, 
	SUM(totpenfodds)      AS 'totpenfodds', 
    	SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
	   SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report 
where reg = 'c' and area='01'
group by reg, RegionAcronym, area
union

select  RegionAcronym, '' as Area,  '' as doc, '' as dist,'<a href=distdoclisting.aspx?dist=' + dist + '>' + dist + '</a>' as reg,
SUM(totpenfodds)      AS 'totpenfodds', 
   
		SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
		  SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report where dist='200'
group by reg, RegionAcronym, area, dist

union

select reg, RegionAcronym, area, Dist,  DOC, 
SUM(totpenfodds)      AS 'totpenfodds', 
   
		SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
		  SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report where doc='009' 
group by reg, RegionAcronym, area, Dist, DOC 
order by doc, area, reg,regionacronym, dist 

[code]

I got it to work by forcing letters into the Dist fields
and doing an order by dist but is there another way to do this?  What get's counted first black spaces, letters then numbers?

 

SELECT '' AS doc, RegionAcronym, '' as area, 'A' as dist,'<a href=region.aspx?Reg=' + Reg + '>' + RegionAcronym + '</a>' as reg,
       SUM(totpenfodds)      AS 'totpenfodds', 
       SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
	   SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
FROM   T16Report 
where reg='c'
group by   RegionAcronym,reg

union

select '' as doc,RegionAcronym, Area, 'b' as Dist,'<a href=areadistlisting.aspx?reg=' + reg + '&Area=' + Area + '>' +'Area ' + right(area,1) + '</a>' as reg, 
	SUM(totpenfodds)      AS 'totpenfodds', 
    	SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
	   SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report 
where reg = 'c' and area='01'--@area
group by reg, RegionAcronym, area
union

select  RegionAcronym, '' as Area,  '' as doc, 'd' as dist,'<a href=distdoclisting.aspx?dist=' + dist + '>' + dist + '</a>' as reg,
SUM(totpenfodds)      AS 'totpenfodds', 
   
		SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
		  SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report where dist='200'
group by reg, RegionAcronym, area, dist

union

select reg, RegionAcronym, area, 'e' as Dist ,  DOC, 
SUM(totpenfodds)      AS 'totpenfodds', 
   
		SUM(pendinfo1) AS 'pendinfo1',
	   SUM(pendndds)       AS 'pendndds', 
		  SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 
       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400'
from t16report where doc='009'
group by reg, RegionAcronym, area, Dist, DOC 
order by dist

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-23 : 01:27:01

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect AND use ISO-8601 temporal formats, codes AND so forth. People cannot read your mind, so post your code AND clear specs if you really want help.

Your data element names were vague and illegal.

What you posted makes no sense in an SQL framework. The data element names are vague when they are even close to useable. You used needless single quotes for improper aggregate names.

You do not seem to know that UNION removes redundant rows. And the result set will not have all of those silly “SUM(something) AS something_tot” names.

No good SQL programmer would have XML in his code.

Your code and no DDL is such a confused mess I cannot figure out what you trying to do, I would guess that you have some kind of ROLLUP hidden in this.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources