Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 - View - Order by Problem Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 16, 2011 5:36 AM
Points: 2, Visits: 3
I created a view using order by Statement. But it displaying randomly not in order. SQL 2008 R2 using . Installed CU1,CU2,CU3,CU4 and CU5. Still the problem exist.

CREATE VIEW [dbo].[View_1]
AS
SELECT TOP (100) PERCENT MediId, MediName, UnitSize
FROM dbo.Medicines
ORDER BY MediName


SELECT [MediId]
,[MediName]
,[UnitSize]
FROM [GBSMedi].[dbo].[View_1]


MediId MediName UnitSize
1 REFRESH TEARS 1
2 GENTEAL EYE DROPS 1
3 Gelusil MPS 10
4 GENTAMYCIN 1

For Correct result I have to use

SELECT * FROM [GBSMedi].[dbo].[View_1] order by MediName
MediId MediName UnitSize
3 Gelusil MPS 10
4 GENTAMYCIN 1
2 GENTEAL EYE DROPS 1
1 REFRESH TEARS 1

Order by statement already used in view ,instead of that we have to use it in every view statement in front end.

After upgrading from SQL 2000 to SQL 2008 , am facing this problem . Using morethan 100 views. So its not easy to recode it in front end .

Please help me to solve this issue
Post #1046399
Posted Wednesday, January 12, 2011 5:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
Order by is not honoured anywhere other than in the outer-most select statement, that is, the one that selects from the view. This is not a bug, it's intentional. The fix is to move the Order By from the view into the select that queries the views.

It was a bug in SQL 2000 that resulted in views returning data ordered. The bug was fixed in SQL 2005.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1046404
Posted Wednesday, January 12, 2011 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 16, 2011 5:36 AM
Points: 2, Visits: 3
Thank u Sir for ur reply
Post #1046415
Posted Thursday, August 25, 2011 5:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 4:19 AM
Points: 45, Visits: 111
Interestingly, although using Select TOP 100 PERCENT with an ORDER BY clause results in the sort order being ignored, specifying Select TOP 99.999999 PERCENT will honour the ORDER BY.
I'm not for one minute advocating this as a safe solution, just that it seems like inconsistent behaviour.
Post #1165242
Posted Thursday, August 25, 2011 5:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
TOP (99.9999) PERCENT is a row-limiting TOP (if the row count is large enough and SQL cannot assume that it's not). TOP (x) ORDER BY is ignored for non-row limiting TOP (ie 100 as that's the only one that guaranteed does not limit the rows)




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1165249
Posted Thursday, August 25, 2011 5:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 AM
Points: 1,361, Visits: 15,264
GilaMonster (1/12/2011)
Order by is not honoured anywhere other than in the outer-most select statement, that is, the one that selects from the view. This is not a bug, it's intentional. The fix is to move the Order By from the view into the select that queries the views.

It was a bug in SQL 2000 that resulted in views returning data ordered. The bug was fixed in SQL 2005.



I remember the day I upgraded and found developers doing this.
Post #1165256
Posted Thursday, August 25, 2011 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 10, 2011 5:31 AM
Points: 30, Visits: 64
Remember that select define execution plan, soo order by can change. That's way you need to put it in select statment.
-
Lic. Andrés M. Aiello
DBA MSSQL - Oracle
http://aiellodba.blogspot.com/
Post #1165321
Posted Thursday, March 29, 2012 11:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 29, 2012 12:41 PM
Points: 1, Visits: 1
The view will order correctly if you use 'Select top 100000000000' or whatever number of records that you are sure to return all.
Post #1275266
Posted Thursday, March 29, 2012 12:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Treat the view as a table, put the order by on the select from the view, not in the view.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1275289
Posted Thursday, March 29, 2012 12:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 13,111, Visits: 11,946
Lynn Pettis (3/29/2012)
Treat the view as a table, put the order by on the select from the view, not in the view.


+1


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1275291
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse