SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Problem in my SQL..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sniper83
Starting Member

5 Posts

Posted - 11/23/2007 :  08:21:04  Show Profile  Reply with Quote
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

India
22773 Posts

Posted - 11/23/2007 :  08:46:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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
Go to Top of Page

Sniper83
Starting Member

5 Posts

Posted - 11/23/2007 :  09:46:44  Show Profile  Reply with Quote
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..
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/23/2007 :  09:51:39  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/23/2007 :  14:33:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use comma!

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



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Sniper83
Starting Member

5 Posts

Posted - 11/27/2007 :  08:57:10  Show Profile  Reply with Quote
Thanks alot both of you.. It was the "," I was missing.. :)
Go to Top of Page

ManojPatel
Starting Member

USA
1 Posts

Posted - 03/24/2013 :  10:48:40  Show Profile  Reply with Quote
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"


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000