Windowed Function in Where Clause

Question

Windowed Function in Where Clause

irobertson

Hall of Fame

Points: 3695
More actions
March 5, 2014 at 7:54 am

#150634

So, as far as I know (and as I've just been attempting to prove to a member of my client's staff), you can't use a windowed function in a where clause. Nor can you directly refer to a column alias in the where clause either. So why do these queries return results?
create table #OrderingTest (Person varchar(10), Score int)
insert #OrderingTest values ('Bob',11)
insert #OrderingTest values ('Sue',21)
insert #OrderingTest values ('Dave',21)
insert #OrderingTest values ('Lucy',31)
-- shouldn't work
select Person
, OrderingKey = row_number() over (order by Person)
from #OrderingTest
order by OrderingKey desc
-- shouldn't work
select Person
, OrderingKey = row_number() over (order by Person)
from #OrderingTest
order by row_number() over (order by Person) desc
-- should work
; with cte as (
select Person
, OrderingKey = row_number() over (order by Person)
from #OrderingTest
)
select * from cte
order by OrderingKey desc
/*
results:
Person OrderingKey
---------- --------------------
Sue 4
Lucy 3
Dave 2
Bob 1
(4 row(s) affected)
Person OrderingKey
---------- --------------------
Sue 4
Lucy 3
Dave 2
Bob 1
(4 row(s) affected)
Person OrderingKey
---------- --------------------
Sue 4
Lucy 3
Dave 2
Bob 1
(4 row(s) affected)
*/
I've never seen this behaviour before. Has anyone else?
@@version: Microsoft SQL Server 2005 - 9.00.5069.00 (X64)
Aug 22 2012 18:02:46
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't use a windowing function in a where clause. The examples you show work because they're not using a windowing function in a where clause, they're using it in an Order By and that's allowed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

irobertson Hall of Fame Points: 3695 More actions · Answer 2

irobertson

Hall of Fame

Points: 3695

March 5, 2014 at 8:33 am

#1694341

Goodness me, so they are. Oh. *cough*

:blush:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

:hehe:

btw, these are valid

with cte as (

select Person

, row_number() over (order by Person) AS OrderingKey

from #OrderingTest

)

select * from cte

where OrderingKey = 5;

select * from (

select Person

, row_number() over (order by Person) AS OrderingKey

from #OrderingTest

) sub

where OrderingKey = 5;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Maybe this is a silly question but:

select Person

, OrderingKey = row_number() over (order by Person)

from #OrderingTest

order by row_number() over (order by Person) desc

What's the difference between that and this?

select Person

from #OrderingTest

order by Person desc

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

irobertson Hall of Fame Points: 3695 More actions · Answer 5

Not a silly question Dwain. The ordering was supposed to be by Score, not Person. Which would make more sense.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

OK thanks. My head was spinning like Linda Blair's in The Exorcist trying to work out the possibilities. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

dwain.c (3/6/2014)
OK thanks. My head was spinning like Linda Blair's in The Exorcist trying to work out the possibilities. 😛

My head was spinning like Dwain Camps's in a sweetshop trying to work out the possibilities 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden