In SQL Server How can I use ROW_Number() with a subquery column?

Question

Here's my query:

DECLARE @StartRow INT
DECLARE @PageSize INT
SET @StartRow = 1
SET @PageSize = 5

SELECT ContractID,Property FROM
(
    SELECT c.ContractID, 
    Property = 
    (
        SELECT TOP 1 p.Name 
        FROM Com.Property p 
        JOIN VContract.Contract2Property c2p ON c2p.PropertyID=p.PropertyID 
        WHERE c2p.ContractID=c.ContractID
    ),
    ROW_NUMBER() OVER (ORDER BY Property) as RowNum
    FROM VContract.[Contract] c
) as sub
WHERE RowNum BETWEEN @StartRow AND ((@StartRow + @PageSize) - 1)

The problem is the (ORDER BY Property) piece. I can order by c.ContractID but not Property. So how can this be accomlished? I need to lookup the property's name and then I wish to sort by that name.

This is used to populate a website so the paging is important to me so I can limit how many records are returned a one time.

Thanks for any help.

Answer 1

Try moving your call to ROW_NUMBER() to your outer query:

    SELECT ContractID, Property, ROW_NUMBER() OVER (ORDER BY Property) as RowNum FROM
    (
        SELECT c.ContractID, 
        Property = 
        (
            SELECT TOP 1 p.Name 
            FROM Com.Property p 
            JOIN VContract.Contract2Property c2p ON c2p.PropertyID=p.PropertyID 
            WHERE c2p.ContractID=c.ContractID
        ),
        FROM VContract.[Contract] c
    ) as sub

Note that you may have to pull your where clause out to another layer of nesting.