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

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 b开发者_JAVA百科y 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.

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.

I think when use sub query with CTE concept then you will overcome this problem

WITH VContract AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY Property) as RowNum, ContractID
 FROM
 (
   SELECT c.ContractID AS 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
 )
)
SELECT ContractID FROM VContract 
WHERE RowNum BETWEEN @StartRow AND ((@StartRow + @PageSize) - 1)

I haven't tried this, but... I added a new outer layer which restricts the row number

SELECT ContractID,Property FROM  
(
  SELECT ContractID,Property, 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  
      ),  
      ROW_NUMBER() OVER (ORDER BY Property) as RowNum  
      FROM VContract.[Contract] c  
  ) as sub_inner
) as sub_outer
WHERE RowNum BETWEEN @StartRow AND ((@StartRow + @PageSize) - 1)  

I am guessing the order by does not work because it is a sub select.

Instead of doing a sub select to get the value of the Property name, can you just do a join with Contract to get the name? Then you can order by the name.