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.
精彩评论