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