incorrect 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 2000 Forums

Transact-SQL (2000)

incorrect order by

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-20 : 23:01:21

Hi friends
i created following view and when run it i get the data in ascending order even though i specified as "desc"! am missing anything here
CREATE VIEW Transactions
AS
SELECT TOP(100) PERCENT
[Is A Consultation] = CASE tr.[type] WHEN 'I' THEN 'Yes' ELSE 'No' END,
[Bulk Billed] = CASE tr.[bulk] WHEN 1 THEN 'Yes' ELSE 'No' END,
[Created Date & Time] = tr.[date],
[Last Modified By] = tr.[modifier],
tr.[transactid],
tr.[fk_master_masterid] AS [MasterId]
FROM [dbo].[transactions] AS tr
WHERE tr.[status] < 2
ORDER BY [transactid] DESC

when i run following query it always returns in ascending order !!!
SELECT * FROM Transactions

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-20 : 23:01:44

btw am using sql server 2005 standard edition

Cheers

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-21 : 01:29:35

You dont use ORDER inside your VIEW, you do it when using the VIEW:

SELECT *
FROM Transactions
ORDER BY [transactid] DESC

-- The Heisenberg uncertainty principle also applies when debugging

LazyDragon
Starting Member

30 Posts

Posted - 2006-09-21 : 01:44:29

Well, this is one of those deprecated "features" in SQL server 2005.

LazyDragon

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-21 : 16:45:52

quote:
Originally posted by PSamsig

You dont use ORDER inside your VIEW, you do it when using the VIEW:
SELECT *
FROM Transactions
ORDER BY [transactid] DESC

-- The Heisenberg uncertainty principle also applies when debugging

yes you can if you have "TOP" keyword

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-21 : 16:47:54

quote:
Originally posted by LazyDragon

Well, this is one of those deprecated "features" in SQL server 2005.

LazyDragon

did u mean "order by" feature in view ?
i did not get any errors when i create view though!!
but i got error when i did not specify TOP keyword with an order by !
can you point me a resource on the net abt the deprecated feauture ?
Thanks

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-21 : 16:53:08

just found an article explains this behaviour
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Cheers

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-22 : 03:47:19

A table has no defined order, a view is a virtual table, so a view has no defined order.

As you said your self, if you remove the TOP clause then you get an error, so because you 'fool' the server to accept the ORDER BY the server fools you right back by no using it. You can use TOP/ORDER BY in a view, but it is used for selecting what gets returned, not what order it is returned in.

It is a golden rule in SQL, if you want your result set ordered then you ask for it, and if you dont you may or may not get it in a certain order, but you cant rely on it, and it can change the second after.

-- The Heisenberg uncertainty principle also applies when debugging

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-24 : 16:55:08

Thanks for that Psamsig.
i finally managed to do what i want ,by specifying Row_number() function in view definition. works nicely.

Cheers

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-24 : 19:42:28

quote:
Originally posted by rajani

....works nicely.

Sounds more like it "works due to a side effect of a computationally expensive cheesey hack". You're setting yourself up for a fall...

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-24 : 19:48:10

am doing like this

CREATE VIEW Transactions
AS
SELECT
[RowNumber_id] = ROW_NUMBER() OVER (ORDER BY tr.transactid DESC) ,
[Is A Consultation] = CASE tr.[type] WHEN 'I' THEN 'Yes' ELSE 'No' END,
[Bulk Billed] = CASE tr.[bulk] WHEN 1 THEN 'Yes' ELSE 'No' END,
[Created Date & Time] = tr.[date],
[Last Modified By] = tr.[modifier],
tr.[transactid],
tr.[fk_master_masterid] AS [MasterId]
FROM [dbo].[transactions] AS tr
WHERE tr.[status] < 2

Cheers

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-25 : 19:54:22

Why not just order your select statement? You are missing the point of views completely.
Go and join your view to the master table by MasterID. See what you get. Take a look at your query's cost while you're at it.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-25 : 20:03:45

>> Why not just order your select statement? You are missing the point of views completely.
If i could i would.
This particular view being called from one of generic routines and cant add ORDER BY here.

>>Go and join your view to the master table by MasterID.
just tried that and recvd "order" as expected i.e. DESC

i'd not worry at query cost at this moment bcoz at any point of time we'd be looking at one "master" only and records wont be more than 50.

Cheers

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-27 : 00:08:01

quote:
Originally posted by rajani
If i could i would.
This particular view being called from one of generic routines and cant add ORDER BY here.

Heh heh! Don't you love half arsed generics/frameworks. I can see it now:
Developer: "Hey - use this function - no more scary SQL!! This routine allows you to get out some of the data you want in random order".

I'd push to get this fixed if I were you. Anyway - glad you sort of got what you're after.

JoelMMCC
Starting Member

1 Post

Posted - 2006-09-27 : 16:17:02

The problem is caused by the SQL 2005 query optimizer, which ignores TOP (100) PERCENT as redundant. However, any other value of the TOP clause will work, with or without PERCENT.

To guarantee getting all (or the maximum possible number) of your records back, with sorting applied, simply use TOP MAX(int) or TOP 2147483647 instead of TOP (100) PERCENT. But do this only if you absolutely have to have a sorted View. As others have said, sorted Views are inefficient and can cause compatibility problems down the road. You should do the ORDER BY clause in the query that calls the View.

Note that the da Vinci designer used in SQL Server 2005 Management Studio (Express or full, SP1 or otherwise) and Access 2007 in Project Mode (and earlier tools as well) automatically applies TOP (100) PERCENT to any View with an ORDER BY clause. You need to change this manually. Note also that the designer will show the correct sorted results in its Test Query results pane, but the actual results will be unsorted, if you use TOP (100) PERCENT.

These are bugs that really should’ve been corrected before RTM release, and definitely in SP1 if not before!

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-09-27 : 16:52:04

Thanks Joel.
yah!am running SQL 2005 standard with SP1 installed.
btw i managed to resolve this issue by row_number() function

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources