Order By - 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 2005 Forums

Transact-SQL (2005)

Order By

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2008-10-21 : 13:56:40

Can someone please tell me why my order by clause will not work properly in my view, but the select statement alone works fine.

CREATE VIEW [dbo].[vwAdv_Sites_tmp]
AS
SELECT TOP 100 PERCENT s.SOURCE_FEED_ID, s.TIME_OF_MESSAGE, s.ETL_REC_NO, s.ETL_FILE
FROM dbo.tblNCHESS_Stage s JOIN dbo.lu_Nchess_FacID f ON s.SOURCE_FEED_ID = f.Code
Where (NCDETECT.dbo.BaseDate(s.TIME_OF_VISIT) >= NCDETECT.dbo.BaseDate(f.AdvTest) or s.record_type = 'A18')
and (NOT (f.AdvTest IS NULL)) AND F.LiveDateNchess IS NULL
Order By substring(etl_file,36,12),substring(etl_file,26,5) DESC,source_feed_id,Time_Of_Message,etl_rec_no

Here are two sample rows
101370030 20081020081453-0400 1 e:\sitefiles\ncdetect\nc.valid.phi.200810201248.dat

101370270 20081020080616-0400 1 e:\sitefiles\ncdetect\nc.error.phi.200810201248.dat

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 14:02:10

Do an order by at the time of retrieving.

CREATE VIEW [dbo].[vwAdv_Sites_tmp]
AS
SELECT TOP 100 PERCENT s.SOURCE_FEED_ID, s.TIME_OF_MESSAGE, s.ETL_REC_NO, s.ETL_FILE
FROM dbo.tblNCHESS_Stage s JOIN dbo.lu_Nchess_FacID f ON s.SOURCE_FEED_ID = f.Code
Where (NCDETECT.dbo.BaseDate(s.TIME_OF_VISIT) >= NCDETECT.dbo.BaseDate(f.AdvTest) or s.record_type = 'A18')
and (NOT (f.AdvTest IS NULL)) AND F.LiveDateNchess IS NULL

select * from [dbo].[vwAdv_Sites_tmp] order by blah,blah,blah

Dennis Falls
Starting Member

41 Posts

Posted - 2008-10-21 : 14:15:26

Yes, that will work, but it's not always optional. I was wondering if this may have something to do with SQL Server 2005. I have a nearly identical view running SQL Server 2000 and it sorts properly.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 14:22:27

quote:
Originally posted by Dennis Falls

Yes, that will work, but it's not always optional. I was wondering if this may have something to do with SQL Server 2005. I have a nearly identical view running SQL Server 2000 and it sorts properly.

Don't think its got to do anything with 2k or 2k5. Order by is invalid unless you are using top keyword.

Dennis Falls
Starting Member

41 Posts

Posted - 2008-10-21 : 14:29:21

I am using SELECT TOP 100 PERCENT

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 14:34:39

why?

Dennis Falls
Starting Member

41 Posts

Posted - 2008-10-21 : 14:49:18

Why what?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 14:56:24

why use top 100 percent

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 15:12:14

in SQL Server 2000 using ORDER BY and TOP 100 PERCENT in views seemed to be a loophole that worked fine. In SQL Server 2005 that has been corrected and ORDER BY does not guarantee ordered results. This is described very well in SQL Server 2005 BOL under the section "Sorting Rows with ORDER BY"
:
Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly.

http://msdn.microsoft.com/en-us/library/ms188385(SQL.90).aspx

Dennis Falls
Starting Member

41 Posts

Posted - 2008-10-21 : 15:41:23

Thank you for the explanation. That makes much more sense than why?.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 15:46:03

why?

scnr...

Webfred
Planning replaces chance by mistake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources