ORDER BY not working! - 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

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY not working!

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 13:39:38

I'm trying to create a view using the following code:

quote:
SELECT TOP (100) PERCENT Program, COUNT(Program) AS Total,
(SELECT COUNT(Program) AS Count
FROM dbo.Active_Enrollments_by_Earn
WHERE (CDE_PROJ LIKE 'NC%') AND (Program = List.Program)
GROUP BY Program) AS CDC,
(SELECT COUNT(Program) AS Count
FROM dbo.Active_Enrollments_by_Earn AS Active_Enrollments_by_Earn_1
WHERE (CDE_PROJ LIKE 'WS%0') AND (Program = List.Program)
GROUP BY Program) AS WSC
FROM dbo.Active_Enrollments_by_Earn AS List
GROUP BY Program
ORDER BY Program

On my server, it sorts the resulting view in alphabetical order automatically (it didn't matter if I put "ORDER BY Program"). On my production server, however, it doesn't sort by Program at all and I can't seem to resolve it. HELP!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 13:41:34

When you query the view, add an ORDER BY:
SELECT * FROM YourView ORDER BY Program

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 13:44:23

You cant guarantee the order in which resultset is obtained unless you tell the query engine to order explicitly by using an ORDER BY clause.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 13:46:04

I tried that. Didn't work. I'm using the view to populate an Excel document using OPENROWSET.

quote:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\pwdc-shared\Data\Kronos-Reports\REQUEST REPORTS\FY0708\ACTIVE ENROLLMENTS BY EARN\Active Enrollments by Earn TEMP.xls;',
'SELECT * FROM [temp$]') select * from Active_Enrollments_by_Earn_Counts_temp order by Program

The result isn't sorted either.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 13:50:53

I can make another view that uses ORDER BY on the view above, but I don't feel I should have to.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 13:52:31

Making another view isn't going to solve your problem.

Try running the following in Management Studio:
select * from Active_Enrollments_by_Earn_Counts_temp order by Program

You'll notice that it is completely ordered by Program, so the fact that it isn't ordered in Excel means the problem exists outside of SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 13:59:25

You're correct that another view doesn't work.

When I run the query you suggest, it is in fact sorted. BUT, it won't sort my data when I save it in a view so I feel like it is an SQL Server problem.

What can I do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 14:04:25

Why do you want to store the data sorted in view? you should be worrying about the order only while retrieving the data from the view

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 14:11:05

I'm making a temp table based off the view. That is where I'm pulling my data from for the Excel file. I'm using the data to populate a chart where the color of each bar is significant, thus I need the order to always be the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 14:13:44

Run this query which is far more efficient (no correlated subqueries)


SELECT		Program,
		COUNT(*) AS Total,
		SUM(CASE WHEN CDE_PROJ LIKE 'NC%' THEN 1 ELSE 0 END) AS CDC,
		SUM(CASE WHEN CDE_PROJ LIKE 'WS%0' THEN 1 ELSE 0 END) AS WSC
FROM		dbo.Active_Enrollments_by_Earn
GROUP BY	Program

E 12°55'05.25"
N 56°04'39.16"

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-04-23 : 14:18:26

Peso: Wow. Thanks a million. That works.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-23 : 23:19:41

You will still need the ORDER BY. There is no guarantee your group will come out in order.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources