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

Question

Here is my request:

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 (ORDER BY Property). I can order c.ContractID, but not a property. So how can this be achieved? I need to find the name of the property, and then I want to sort by that name.

This is used to populate the website, so it is important that paging is very important to me, so I can limit how many records are returned once.

Thanks for any help.

Answer 1

Try transferring your call to ROW_NUMBER () in your external request:

  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 need to pull the where clause from another nesting layer.

Answer 2

I think that when you use a sub-request with the concept of CTE, 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) 

Answer 3

I have not tried this, but ... I added a new outer layer that limits the line 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) 

Answer 4

I assume that the order does not work, because it is a sub-choice.

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