January 12, 2011 at 5:04 am
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]
MediIdMediName UnitSize
1REFRESH TEARS 1
2GENTEAL EYE DROPS1
3Gelusil MPS 10
4GENTAMYCIN 1
For Correct result I have to use
SELECT * FROM [GBSMedi].[dbo].[View_1] order by MediName
MediIdMediNameUnitSize
3Gelusil MPS 10
4GENTAMYCIN 1
2GENTEAL EYE DROPS 1
1REFRESH 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
January 12, 2011 at 5:15 am
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
January 12, 2011 at 5:33 am
Thank u Sir for ur reply
August 25, 2011 at 5:37 am
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.
August 25, 2011 at 5:45 am
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
August 25, 2011 at 5:53 am
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. 😀
August 25, 2011 at 7:12 am
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
March 29, 2012 at 11:41 am
The view will order correctly if you use 'Select top 100000000000' or whatever number of records that you are sure to return all.
March 29, 2012 at 12:20 pm
Treat the view as a table, put the order by on the select from the view, not in the view.
March 29, 2012 at 12:21 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 12:24 pm
sdermer (3/29/2012)
The view will order correctly if you use 'Select top 100000000000' or whatever number of records that you are sure to return all.
For now, yes. In the next version of SQL or after a service pack, maybe not.
p.s. year old thread.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply