ORDER BY not being honoured - 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 2008 Forums

Transact-SQL (2008)

ORDER BY not being honoured

Author

Topic

beemd
Starting Member

14 Posts

Posted - 2013-10-23 : 07:07:25

Hi,

I've got a view defined as:

------
SELECT TOP (100) PERCENT MasterDataId, CompanyName, Address1, Address2, Locality, Town, County, Postcode, Region, PremiseType, NoOfEmployees,
BusinessType, SICCode, FinancialYearEnd, Turnover, ProfitBeforeTax, NetWorth, TelephoneNumber, FaxNumber, WebsiteAddress, ContactFullName,
ContactJobTitle, EmailAddress, NoOfEmployeesBandId, TurnoverBandId, ProfitBeforeTaxBandId, NetWorthBandId
FROM dbo.tblMasterData
WHERE (FaxNumber IS NOT NULL) AND (Region = 'London') AND (FaxNumber NOT IN
(SELECT faxnumber
FROM dbo.tblExclusions)) AND (FaxNumber NOT IN
(SELECT faxnumber
FROM dbo.tblFPS))
ORDER BY MasterDataId DESC
---

When I do SELECT * FROM vwLondon the results are not ordered by MasterDataId, they seem to be at random.

Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-23 : 07:26:53

This is because in relational theory a table/view is an UNORDERED set so the TOP and ORDER BY are only allowed in the view to obtain the correct rows. eg SELECT TOP (2) PERCENT will get the top 2% based on the order but not necessarly in the order you expect.

The only reliable way to get an ordered set is to use ORDER BY on the outer most query.
eg


SELECT *
FROM vwLondon
ORDER BY MasterDataId

Prior to SQL2005 ORDER BY in a view would work but this was only a fluck of the implementation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 08:49:23

quote:
Originally posted by beemd

Hi,

I've got a view defined as:

------
SELECT TOP (100) PERCENT MasterDataId, CompanyName, Address1, Address2, Locality, Town, County, Postcode, Region, PremiseType, NoOfEmployees,
BusinessType, SICCode, FinancialYearEnd, Turnover, ProfitBeforeTax, NetWorth, TelephoneNumber, FaxNumber, WebsiteAddress, ContactFullName,
ContactJobTitle, EmailAddress, NoOfEmployeesBandId, TurnoverBandId, ProfitBeforeTaxBandId, NetWorthBandId
FROM dbo.tblMasterData
WHERE (FaxNumber IS NOT NULL) AND (Region = 'London') AND (FaxNumber NOT IN
(SELECT faxnumber
FROM dbo.tblExclusions)) AND (FaxNumber NOT IN
(SELECT faxnumber
FROM dbo.tblFPS))
ORDER BY MasterDataId DESC
---

When I do SELECT * FROM vwLondon the results are not ordered by MasterDataId, they seem to be at random.

Thanks

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 09:45:00

Also see http://blog.sqlauthority.com/2010/08/23/sql-server-order-by-does-not-work-limitation-of-the-views-part-1/

Pinal Dave has an interesting series of articles on the "limitations of views".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources