• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

How to use a calculated value in a Where clause

I'm wanting to use a calculated field in my Where statement, how do I do this without nesting queries?  The below doesn't work for me because the Distinct throws off my row numbers in the final result set, I want to set the row_num there... with something like:

Select Row_Number() Over (Order By person.last_name) as row_num, person.first_name, person.last_name Where row_num > 10

The above gives me an error though saying row_num isn't a known field.  The below is how I tried to get around it, however my row numbers are off because the distinct gets rid of some of them (I need a distinct query as to not have duplicates but I want the row number assigned on that set, not on the original).  The above seems like it should work but I don't know how to Where off of the row_num variable.  I tried putting the Row_Number() in the Where also to no avail.

Here's what I'm using now which is not quiet right:

With SearchResults As ( Select Row_Number() Over(Order By person.last_name) as row_num, person.person_id, person.last_name, person.first_name, address.city, Coalesce(assignment.staff_id, '') As staff_id, Coalesce(person2.preferred_name, 'N/A') As staff_name, 0.00 As household_giving
From person
Inner Join person_address On person_address.person_id = person.person_id
Inner Join address On address.address_id = person_address.address_id
Left Outer Join assignment On person.person_id = assignment.person_id And assignment.organization_code = 'PRIM'
Left Outer Join person person2 On person2.person_id = assignment.staff_id Where address.state_code='IN' And address.zip_code='47401'
) Select Distinct Top 5 person_id, last_name, first_name, city, staff_id, staff_name, household_giving From SearchResults Where row_num > 20 Order By last_name, first_name

I'm using Sql Server 2005 and the .Net Sql Client.
0
IUFITS
Asked:
IUFITS
  • 9
  • 6
1 Solution
 
ryndyeCommented:

Select Row_Number() Over (Order By person.last_name) as row_num, person.first_name, person.last_name Where  Row_Number() Over (Order By person.last_name)  > 10

try above
0
 
IUFITSAuthor Commented:
So I tried that, here was my statement:

Select Row_Number() Over (Order By person.last_name) as row_num, person.first_name, person.last_name From person Where  Row_Number() Over (Order By person.last_name)  > 10

The error it returned was:

Msg 4108, Level 15, State 1, Line 11
Windowed functions can only appear in the SELECT or ORDER BY clauses.
0
 
IUFITSAuthor Commented:
I also tried this.  The row_num field was correct but the Where part always returned 1 when I wanted it to be the value of row_num.

Select Row_Number() Over (Order By person.last_name) as row_num,  person.first_name, person.last_name From person Where (Select Row_Number() Over (Order By person.last_name)) < 10
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ryndyeCommented:
Select b.first_name, last_name
  From (Select Row_Number() Over (Order By person.last_name) as row_num, person.first_name,         person.last_name) b
Where b.row_num > 10

how about this?
0
 
ryndyeCommented:
oh no nested queries.

Why don't you do this

Select Top 10 person.first_name, person.last_name
  From person
Order By person.last_name

0
 
IUFITSAuthor Commented:
Because I want the Row_Number to do paging so I can select TOP 10 that are > 0, TOP 10 that are > 10, TOP 10 that are 20 and so on and so forth.
0
 
IUFITSAuthor Commented:
Your query you said was nested worked.  I also put together this one that returns pretty much the same results as your query.  Is there a benefit to using one over the other?

Select Top 150 person2.row_num2,
         person.person_id,
         person.first_name,
         person.last_name
From person
Inner Join (select person_id, Row_Number() Over (Order By last_name, first_name, person_id) as row_num2 From person) person2 On person2.person_id = person.person_id
Order By person.last_name, first_name, person_id
0
 
IUFITSAuthor Commented:
I just ran your query and my query, which both return the same results.... I used 150 rows as a same and the Executation Path from the Managament studio said mine took 72% of the batch and yours took 28% of the batch so clearly the one you put together runs better.
0
 
ryndyeCommented:
this seems to work well

Select Top 10 Row_Number() Over (Order By last_name) As Row_Num, last_name, first_name
  From person
0
 
IUFITSAuthor Commented:
The problem with that one is that I need to do a Where off of the Row_Number()...

Like, Top 10 Row_Number Over (Order By last_name) As Row_Num, last_Name from person Where Row_Num > 15

I can't see to do that unless I use that nested query you created or the query I created.  Yours works much better from an overhead standpoint.  I'm going to accept that answer because I haven't found another way to do it.  It would be nice if you could just use the calculated value in the Where clause.
0
 
IUFITSAuthor Commented:
Thanks for the help ryndye.
0
 
ryndyeCommented:
hmmm, i guess i miss understood requirement.

row_num > 15 can be

Select Top 15 Row_Number Over (Order By last_name) As Row_Num, last_Name from person

so using Top keyword instead of where.

if you need to have it in WHERE keyword for some reason then i guess this will not work.
0
 
IUFITSAuthor Commented:
the where helps me do ranges... so top 15 Where row_num > 15
top 15 where row_num > 30, etc.

It's for paging on a web-site.
0
 
ryndyeCommented:
oh i see now.

best way to implement server side paging is to use CTE (new in 2005)

See example in

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

hope this helps
0
 
IUFITSAuthor Commented:
That's originally what I had done but when I did a Dinstinct on the last select it caused it to skip numbers since they were assigned in the orignal query.  I couldn't put a distinct on the query with the row_number because the row_number would make it unique... hence why I asked my question.  :)

The distinct totally threw the whole thing off, if it wasn't for that the link you provided was the best solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now