Sort Order - 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

General SQL Server Forums

New to SQL Server Programming

Sort Order

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 04:16:52

How do I create a sort order column in a view? I could use Row_Number() in 2005 but unfortunately I need to create a sort order column in a 2000 view and the View is not letting me to use an ORDER BY.

Any inputs?

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 04:21:15

include a TOP 100 PERCENT and you can use an ORDER BY.

But the real question is "WHY"?
Why is the order in the view important. ORDER BY is almost always a front-end decision.
The ORDER BY should only be usewd once, in the final SELECT which returns a resultset.

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 04:24:47

Hi Peso,

I using this view to create a SSAS dimension and the dimension needs a oRder by similar to "ORDER BY
LEFT (NAME,1),
NAME DESC" and there is no way I can achieve this in SSAS.

Do let me know if you have any other thoughts.

Thanks!

Prakash.P
The secret to creativity is knowing how to hide your sources!

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 04:27:36

Suprisingly, the TOP 100 PERCENT does not work for me. Not sure why

Prakash.P
The secret to creativity is knowing how to hide your sources!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 04:34:16

What's error you're getting?

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 04:40:33

No error, but the data is not sorted as per expected in the view. But if I use the same Order by in a normal query it works as expected

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 04:50:32

I told you.
SQL Server 2005 does not use ORDER BY in views.
Views are normally not materialized.

Use ORDER BY when including the VIEW in the SELECT statement.

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 04:59:07

Guys, I am sorry, TOP 100 PERCENT works in 2000 and not in 2005!

Peso, I am not sure if you have worked on SSAS, but the fact is that SSAS has very few oprions to specify a ORDER BY for dimension members and hence there is no other way to do this other than TOP 100 PERCENT. Many thansk for your replies!

Prakash.P
The secret to creativity is knowing how to hide your sources!

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 05:38:49

Again an issue, Now i need to create 2 SORT Orders, 1 by Asc and another by Desc. Is there any way to do this in 2000?

Prakash.P
The secret to creativity is knowing how to hide your sources!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 05:41:27

ORDER BY Firstfield ASC,SecondField DESC

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 06:01:20

quote:
Originally posted by visakh16

ORDER BY Firstfield ASC,SecondField DESC

Hi Visakh,

I will explain my requirement with an example

Name Dept
A BCD
C ABC
B CDE

I need 2 more columns wich will give the sort orders like(Ordering as Based on NAME column)

Name Dept Sort_Order Sort_Order_Desc
A BCD 1 3
B CDE 2 2
C ABC 3 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 06:07:32

Are you using SQL 2005? if yes use ROW_NUMBER() function

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-22 : 06:57:00

quote:
Originally posted by pravin14u

How do I create a sort order column in a view? I could use Row_Number() in 2005 but unfortunately I need to create a sort order column in a 2000 view and the View is not letting me to use an ORDER BY.

Any inputs?

Prakash.P
The secret to creativity is knowing how to hide your sources!

Prakash.P
The secret to creativity is knowing how to hide your sources!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 07:57:40

This won't perform well, but it will do what I think you're asking...

declare @t table (Name varchar(10),  Dept varchar(10))
insert @t
          select 'A', 'BCD'
union all select 'C', 'ABC'
union all select 'B', 'CDE'

select *, (select count(*) from @t where Name <= a.Name) as Sort_Order,
          (select count(*) from @t where Name >= a.Name) as Sort_Order_Desc
from @t a

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 12:33:19

Have you tried to do sorting/ordering is SSAS? I'm pretty sure you can order each dimension's attribute is several ways. It's been a while since I had to mess with any artificial ordering, but if you are ordering by Name, then the SSAS should be able to handle that for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources