Prev: Query Optimization
Next: Get Total Rows.
From: Plamen Ratchev on 26 Mar 2010 18:23
Yes, you need the use the same ORDER BY expressions in ROW_NUMBER.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 26 Mar 2010 19:30
OK,

But in my OrderBy expression that I mention:

ORDER BY IsNull(VenName, 'zz9'),EnvID, IsLocked Desc,
IsNull(OtherName,'zz9'), Name, Desc

I tried to do the same thing in my Row_NUMBER,

ROW_NUMBER() OVER (ORDER BY IsNull(VenName, 'zz9'),EnvID, IsLocked Desc,
IsNull(OtherName,'zz9'), Name, Desc)

But I got an Ambiguous column name error for each of the names. I don't get
that with the query ORDER BY.

Also, if I do the ORDER BY in the OVER() clause do I need to do it in the
query clause as well if it is going to order it anyway?

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:hYGdnd82a5pZrjDWnZ2dnUVZ_gKdnZ2d(a)speakeasy.net...
> Yes, you need the use the same ORDER BY expressions in ROW_NUMBER.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 26 Mar 2010 19:59
tshad wrote:
> I tried to do the same thing in my Row_NUMBER,
>
> ROW_NUMBER() OVER (ORDER BY IsNull(VenName, 'zz9'),EnvID, IsLocked Desc,
> IsNull(OtherName,'zz9'), Name, Desc)
>
> But I got an Ambiguous column name error for each of the names. I don't get
> that with the query ORDER BY.
>

Did you add an alias for the expression? I just tried this and it works fine (note that [Desc] is reserved keyword):

SELECT
ROW_NUMBER() OVER (ORDER BY IsNull(VenName, 'zz9'),EnvID, IsLocked Desc,
IsNull(OtherName,'zz9'), Name, [Desc]) AS column_name
FROM Foo;

> Also, if I do the ORDER BY in the OVER() clause do I need to do it in the
> query clause as well if it is going to order it anyway?
>

Yes, you do need to use ORDER BY in the query. As I already noted ORDER BY in OVER is only for the ROW_NUMBER function,
not for the final result set. Even if the result set appears sorted it is not guaranteed unless you use ORDER BY in the
query. See more here:
http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx


--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  | 
Pages: 1 2
Prev: Query Optimization
Next: Get Total Rows.