VIEW - 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

General SQL Server Forums

New to SQL Server Programming

VIEW - ORDER BY

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2010-01-07 : 04:14:12

Hello Sir ,

How we apply order by in view .

I am using this .

SELECT *, RANK() OVER (PARTITION BY [FirstName] ORDER BY [FirstName]) AS RANK
FROM dbo.tbl_user

but it is fail

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:15:21

do you mean this?


SELECT *, RANK() OVER (PARTITION BY [FirstName] ORDER BY [FirstName]) AS RANK 
FROM dbo.tbl_user 
ORDER BY [RANK]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:16:10

or just


SELECT *, RANK() OVER (ORDER BY [FirstName]) AS RANK 
FROM dbo.tbl_user 
ORDER BY [RANK]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-07 : 04:16:27

I can see no VIEW?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:19:45

even this will be enough for you


SELECT *
FROM dbo.tbl_user 
ORDER BY [FirstName]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 04:22:41

quote:
Originally posted by yaman

Hello Sir ,

How we apply order by in view .

I am using this .

SELECT *, RANK() OVER (PARTITION BY [FirstName] ORDER BY [FirstName]) AS RANK
FROM dbo.tbl_user

but it is fail

Yaman

What do you mean by fail?
Post the full code you used

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:29:51

also if you were trying to do order by inside view definition it does not guarantee that results returned by view will be in order in sql 2005/2008. for that you need to use explicit order by in select statement that retrieves data from the view.
please not that this behavior was different in SQL 2000 and you can get that behavior by applying the below hotfix

http://support.microsoft.com/kb/926292

yaman
Posting Yak Master

213 Posts

Posted - 2010-01-07 : 05:30:21

quote:
Originally posted by visakh16

also if you were trying to do order by inside view definition it does not guarantee that results returned by view will be in order in sql 2005/2008. for that you need to use explicit order by in select statement that retrieves data from the view.
please not that this behavior was different in SQL 2000 and you can get that behavior by applying the below hotfix

http://support.microsoft.com/kb/926292

Thanks Sir .

I got one solution :-

ALTER view [dbo].[VW_IF_#_4]
as
SELECT Firstname FROM (SELECT TOP 100 Firstname FROM tbl_User order by FirstName ASC)V1

It is running .

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 05:33:49

quote:
Originally posted by yaman

quote:
Originally posted by visakh16

also if you were trying to do order by inside view definition it does not guarantee that results returned by view will be in order in sql 2005/2008. for that you need to use explicit order by in select statement that retrieves data from the view.
please not that this behavior was different in SQL 2000 and you can get that behavior by applying the below hotfix

http://support.microsoft.com/kb/926292

Thanks Sir .

I got one solution :-

ALTER view [dbo].[VW_IF_#_4]
as
SELECT Firstname FROM (SELECT TOP 100 Firstname FROM tbl_User order by FirstName ASC)V1

It is running .

Yaman

make sure you read KB article and understand it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources