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

sql-server

Solution

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.

Problem

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.

Original source