Hi, I have a problem generating an SQl sentence:What would be good to me would be to construct two WITH statements. My SQL looks like this:WITH TT As(SELECT *,(SELECT Number From [TASKS.Index] WHERE ID = S.ID) AS FriendID, HasFiles = CASE WHEN (SELECT Top 1 ID From [TASKS.AttachedFiles]WHERE UniqueID = S.ID) IS NULL THEN 0 ELSE 1 END From [TASKS.Support] AS S )WITH TT2 As(SELECT *, ROW_NUMBER() OVER (ORDER BY [FriendID] desc) AS RowNumber FROM TT ORDER By FriendID DESC)SELECT * FROM TT2 WHERE RowNumber BETWEEN 5 AND 8
I simply want to add rowNumber to the first select and then use some data between some rownumbers. I have tried to put Rownumber directly into the first WITH-statement, but the rownumbers is just set to 1.This and many like it have I tried:WITH TT As(SELECT *,(SELECT Number From [TASKS.Index] WHERE ID = S.ID) AS FriendID, (SELECT ROW_NUMBER() OVER (ORDER BY FriendID desc) From [TASKS.Index] WHERE ID = S.ID) AS RowNumber,HasFiles = CASE WHEN (SELECT Top 1 ID From [TASKS.AttachedFiles]WHERE UniqueID = S.ID) IS NULL THEN 0 ELSE 1 END From [TASKS.Support] AS S )SELECT * FROM TT WHERE RowNumber BETWEEN 1 AND 6WITH TT2 As(SELECT *, ROW_NUMBER() OVER (ORDER BY [FriendlyID] desc) AS RowNumber FROM TT ORDER By FriendID DESC)SELECT * FROM TT2 WHERE RowNumber BETWEEN 1 AND 6
Can sombydy tell me how I can do this and if I can make to WITH-statements in some kind of way..?