SQL OVER(PARTITION BY...)

Does anyone know if it is possible to perform a SQL OVER(PARTITION BY...) as a subselect? I want to only perform the OVER(PARTITION BY for the row that is being selected in a SELECT statement. I would rather not execute a OVER(PARTITION BY over the whole second table, since it causes 166,000,000+ rows to be partitioned that don't need to be.

I am selecting rows from a main table, and using a column in that table to narrow down the PARTITION BY to only select rows in the second table that match what is selected from the main table. I need the partition by as a further criteria on the main table select so I only include rows from the main where a specific selection exists in the second table, and that condition needs to be set up as a partition by.

For example:

Select a, b, c, d from tablea where
A = 100 and
B = 2 and
C in (select * from (OVER(PARTION BY SYNTAX built only over rows in tableb that have a value in a column ZZ that equals the value in C)))

We are on 7.1

Thank you,

Doug