SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order By - Fail
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

417 Posts

Posted - 10/09/2013 :  05:22:20  Show Profile  Reply with Quote
Hi,

I have the following query which works fine in DESIGN mode. When I run the query details are sorted in OrganisationName as required. However, when I option to right click and select Open View it is NOT. Instead it sorts it with the first Field which is Primary key and ID (OrganisationID).. I dont understand this behaviour..

SELECT     TOP (100) PERCENT dbo.tblOrganisation.OrganisationName, dbo.tblOrganisation.Country, dbo.tblOrganisation.LicenceEffectiveDate, 
                      dbo.tblOrganisation.LicenceExpiryDate, dbo.tblOrganisation.DateDisabled, dbo.tblOrganisation.CreationDate, 
                      dbo.tblCurrSelectedStatus.Curr_Status
FROM         dbo.tblOrganisation INNER JOIN
                      dbo.tblCurrSelectedStatus ON dbo.tblOrganisation.Status = dbo.tblCurrSelectedStatus.Curr_Status
ORDER BY dbo.tblOrganisation.OrganisationName


Thank you

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  05:42:09  Show Profile  Reply with Quote
its by design
see

http://visakhm.blogspot.com/2010/01/behaviour-of-order-by-inside-view.html

for getting correct order from view you need to use ORDER BY in statement which selects from view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Imad
Starting Member

Pakistan
1 Posts

Posted - 10/09/2013 :  08:00:35  Show Profile  Reply with Quote
Hi,

It is because views did not support order by clause. Try to create view without order by clause and then write select from view with order clause. It will work for you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  08:06:45  Show Profile  Reply with Quote
quote:
Originally posted by Imad

Hi,

It is because views did not support order by clause. Try to create view without order by clause and then write select from view with order clause. It will work for you.


They used to support it in SQL 2000
See the posted link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000