Problem in my SQL..? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in my SQL..?

Author

Topic

Sniper83
Starting Member

5 Posts

Posted - 2007-11-23 : 08:21:04

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 6


WITH 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..?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-23 : 08:46:06

ex

WITH TT As
(
select row_number() over (order by name) as rnum, * from sysobjects
)

select * from TT
where rnum between 1 and 6

Madhivanan

Failing to plan is Planning to fail

Sniper83
Starting Member

5 Posts

Posted - 2007-11-23 : 09:46:44

quote:
Originally posted by madhivanan

ex

WITH TT As
(
select row_number() over (order by name) as rnum, * from sysobjects
)

select * from TT
where rnum between 1 and 6

Madhivanan

Failing to plan is Planning to fail

Thanks, but I'm aware of that.. What I'm looking for is a way to get some data linked with another table and then use this rownumber method which i can use to sort based on the rows. It is exactly what i'm doing in the first one and the TT is working, but I cannot use it in/after TT2 to get data based on the rows because the second WITH is not allowed..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-23 : 09:51:39

use derived table instead of CTE for TT ?


WITH TT2 As
(
	SELECT 	*, 
		ROW_NUMBER() OVER (ORDER BY [FriendID] desc) AS RowNumber 
	FROM 	
	(
		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 
	) TT
)
SELECT 	* 
FROM 	TT2 
WHERE 	RowNumber BETWEEN 5 AND 8

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-23 : 14:33:52

Use comma!

;with tt (...)
AS ( select .... )
, yy (...)
AS (select ....)

E 12°55'05.25"
N 56°04'39.16"

Sniper83
Starting Member

5 Posts

Posted - 2007-11-27 : 08:57:10

Thanks alot both of you.. It was the "," I was missing.. :)

ManojPatel
Starting Member

1 Post

Posted - 2013-03-24 : 10:48:40

Thank you Peter. You are a life saver. I was trying to find an answer to calculate "Average Days Paid" for list of Vendors/Paid invoice. By the way this is my first ever reply to any blog!!!

quote:
Originally posted by SwePeso.

Use comma!

;with tt (...)
AS ( select .... )
, yy (...)
AS (select ....)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources