ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,146 through 8,160 (of 10,143 total)

RE: delay expired problem in query

djbuzz (2/5/2010)
Hi
This query does not work in my SQL 2008 Server:
SELECT dbo.ip2Tags.ip, dbo.ip2Tags.MediaId
FROM dbo.ip2Tags LEFT OUTER JOIN
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 5, 2010 at 7:15 am

#1114411
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Paul White (2/3/2010)
Mark-101232 (2/3/2010)
Might be of interest
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=318084
Also https://connect.microsoft.com/SQLServer/feedback/details/496271/row-number-does-not-number-properly-intermideate-cte-result-set
Hi Paul
This has turned out to be a real can of worms. I couldn't see anything wrong using the example code and data...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 5, 2010 at 5:43 am

#1114348
RE: Where are you?

Andeavour (2/4/2010)
When I was working at CRS on Dantzic Street they were filming Cracker in the old Maxwell House building before it was turned into The Printworks. Never saw Robbie...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 5, 2010 at 2:04 am

#1114250
RE: Recursive CTE: Is it possible to count records without an aggregate function?

sgmunson (2/4/2010)
row_number() does not get properly evaluated - it's as if it gets a fresh evaluation from scratch for each row in the iteration
But what if it's correctly "evaluating" only...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 4, 2010 at 7:23 am

#1113743
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Paul White (2/3/2010)
Mark-101232 (2/3/2010)
Might be of interest
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=318084
Also https://connect.microsoft.com/SQLServer/feedback/details/496271/row-number-does-not-number-properly-intermideate-cte-result-set
Thanks for posting this, Paul, it's made for a very interesting lunchtime.
If you add a third anchor row, like this...
with T(i, rn, lvl)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 4, 2010 at 7:17 am

#1113739
RE: Recursive CTE: Is it possible to count records without an aggregate function?

The lvl value should be 3 where the parentid value is 3. The windowing function worked fine, but the lvl increment in the recursive part of the CTE didn't.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2010 at 10:24 am

#1113050
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Mark-101232 (2/3/2010)
Might be of interest
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=318084
Thanks for this, Mark. Interesting note - the example query actually does run in compatibility 100 mode.
Edit: On second thoughts, that's quite scary, expecially considering the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2010 at 8:56 am

#1112984
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Might have a play with this at home tonight, it looks promising. This method works as a single-query solution but it looks horrible.
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2010 at 8:09 am

#1112925
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Hi Steve
As promised.
Here's the multistatement code:
-- create table for results
CREATE TABLE #Iteration (PreferenceId INT, BoxId INT, BallId INT)
-- four iterations to solve:
INSERT INTO #Iteration
SELECT PreferenceId, BoxId, BallId
FROM ( -- x
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2010 at 2:28 am

#1112711
RE: Recursive CTE: Is it possible to count records without an aggregate function?

<<limit the recursive selection to rows that aren't already in our selection>>
Hi Steve
I'm not sure a recursive CTE will easily lend itself to this, because what your recursive query 'sees'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 2, 2010 at 10:05 am

#1112390
RE: Recursive CTE: Is it possible to count records without an aggregate function?

Matt Miller (#4) (2/1/2010)
I'm having a bit of a low-brainpower day, but I think you can achieve what you're looking for using ROW_NUMBER(). If you use whatever you were...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 2, 2010 at 7:03 am

#1112241
RE: grouping my result set based on the firstname column with all other columns included

Nabha (1/27/2010)
Not sure if I am missing something here but isnt it the same query that was posted earlier here
I guess discussion was already started there!
Guess the OP didn't like...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 27, 2010 at 8:11 am

#1109318
RE: Help with a join

Jpotucek (1/27/2010)
so how do I get my query to actually populate History_YN with either blank, Y or N?
Take it out of the WHERE clause. You must have had it in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 27, 2010 at 7:59 am

#1109309
RE: grouping my result set based on the firstname column with all other columns included

klineandking (1/27/2010)
i dont know how alll i want is the result set to grouped on the names,i know its difficult i cannot come up with the solution myself thats why...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 27, 2010 at 7:48 am

#1109302
RE: Help with a join

Jpotucek (1/27/2010)
SELECT a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
,a.history_YN
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
AND...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 27, 2010 at 7:36 am

#1109296

Viewing 15 posts - 8,146 through 8,160 (of 10,143 total)