SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 - View - Order by Problem


SQL 2008 - View - Order by Problem

Author
Message
ravi.pta
ravi.pta
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 4
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228278 Visits: 46340
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, MVP, M.Sc (Comp Sci)
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


ravi.pta
ravi.pta
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 4
Thank u Sir for ur reply
Derek Robinson
Derek Robinson
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 116
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228278 Visits: 46340
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, MVP, M.Sc (Comp Sci)
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


MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5291 Visits: 15346
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. :-D
Aiello DBA
Aiello DBA
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 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/
sdermer
sdermer
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96373 Visits: 38981
Treat the view as a table, put the order by on the select from the view, not in the view.

Cool
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63570 Visits: 17966
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search