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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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..?
 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
22864 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
17689 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
30421 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  
 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.29 seconds. Powered By: Snitz Forums 2000