how to use rank() in where clause

[BHScripter]

Hi:
I have a SQL script where I use in the select statement:

rank () over (partition by t.person order by lp.dsumest) as rankedbyrent


I would like to break up this query into multiple union statements, so I can add a hardcoded "type" field for each select statement section based on multiple factors. The first item I would like to put into the where clause to create a 'rank1' type is the rank() statement above as:

where rank () over (partition by t.person order by lp.dsumest) = 1

when I do this, I get the following error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Is there a way around this?

 

[bborissov]

SELECT * 
FROM (SELECT ......, RANK() OVER ..... AS Test1
            FROM ...) Tbl1 
WHERE Test1 = ???

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[BHScripter]

Yes!!!!!! Thank you so much