[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to use a calculated value in a Where clause

Posted on 2007-07-31
15
Medium Priority
?
258 Views
Last Modified: 2012-08-13
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
Comment
Question by:IUFITS
  • 9
  • 6
15 Comments
 
LVL 4

Expert Comment

by:ryndye
ID: 19601951

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
 
LVL 3

Author Comment

by:IUFITS
ID: 19602687
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19602697
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 4

Accepted Solution

by:
ryndye earned 2000 total points
ID: 19603054
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
 
LVL 4

Expert Comment

by:ryndye
ID: 19603080
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603097
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603121
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603146
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
 
LVL 4

Expert Comment

by:ryndye
ID: 19603158
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603321
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603338
Thanks for the help ryndye.
0
 
LVL 4

Expert Comment

by:ryndye
ID: 19603386
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603393
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
 
LVL 4

Expert Comment

by:ryndye
ID: 19603643
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
 
LVL 3

Author Comment

by:IUFITS
ID: 19603690
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question