ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,826 through 9,840 (of 10,143 total)

RE: Help Optimizing Query?

So you want the cases having their most recent step in your list of stepnames? Sorry to be a pain!
^{“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

July 1, 2008 at 10:41 am

#836810
RE: Help Optimizing Query?

Cool, many thanks Pop.
Got a question for you:
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

July 1, 2008 at 9:16 am

#836770
RE: eliminating duplicates

Oh all right then. No funny stuff either.
DECLARE @ItemID INT
DECLARE @FirstCol varChar(5), @SecondCol INT, @ThirdCol Char(1)
SET @ItemID = 1
UPDATE #Testing SET ThirdCol = CASE @ItemID WHEN 1 THEN 'D'...
^{“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

July 1, 2008 at 8:05 am

#836702
RE: quirk of nulls & blanks using aggregate function

Hi Tim
I use the ASCII function for this, usually the first two characters of the column...
DECLARE @tempvar VARCHAR(5)
SET @tempvar = CHAR(10) + CHAR(13)
SELECT ASCII(LEFT(@tempvar,1)), ASCII(SUBSTRING(@tempvar,2,1))
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

July 1, 2008 at 7:18 am

#836658
RE: eliminating duplicates

Shaun McGuile (7/1/2008)
the point of the exercise -
...so all that is left is a table full of records which were duplicated at least once!
Misleading title though....;)
Jeff, Chris; It falls...
^{“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

July 1, 2008 at 7:10 am

#836649
RE: eliminating duplicates

lmarcum (7/1/2008)
It is not impossible. Add an identity column. Each record will be numbered. Then use the identity column to delete the duplicate records.
Read the OP's second...
^{“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

July 1, 2008 at 6:54 am

#836625
RE: eliminating duplicates

Jeff Moden (7/1/2008)
You guys are missing the point of this particular exercise... despite the name of the post, this is NOT about the elimination of duplicates. Here's what 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

July 1, 2008 at 6:52 am

#836623
RE: Help Optimizing Query?

Pop
Your sample data doesn't return any rows. This chunk...
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

July 1, 2008 at 4:09 am

#836531
RE: eliminating duplicates

mlando.mngomezulu (7/1/2008)
Actually the first step makes sure that the temptable only have one record per corresponding duplicate records. This is achieved by the "group by" clause. This is grouped...
^{“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

July 1, 2008 at 2:34 am

#836507
RE: eliminating duplicates

mlando.mngomezulu (7/1/2008)
Step1: You need to create a table containing only the duplicate records by first selecting all the duplicate records into a new table i.e.
Insert into temptable...
^{“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

July 1, 2008 at 2:04 am

#836488
RE: eliminating duplicates

Nicking Barry's excellent idea (and Janines test data), here's another way which is quite fast:
DROP TABLE #Testing
CREATE TABLE #Testing (
FirstCol varChar(5),
...
^{“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

June 30, 2008 at 10:28 am

#836251
RE: what date format is this?

Jeff Moden (6/26/2008)
Heh... no problem... if you think no coffee is bad, I'm trying to quit smoking... got anyone you want to take to a porkchop dinner? 😀
Ahah that explains...
^{“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

June 27, 2008 at 4:56 am

#835250
RE: INNER JOIN on TOP 1

Mark (6/24/2008)
Another alternative
SELECT
A.RetailerID,
X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B...
^{“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

June 24, 2008 at 8:35 am

#833349
RE: INNER JOIN on TOP 1

Hi Simon
This is simple but somewhat restrictive:
SELECT A.RetailerID, MIN(B.RetailerIDTheirs) AS RetailerIDTheirs
FROM #TableA A
LEFT JOIN #TableB B ON B.RetailerID = A.RetailerID
GROUP BY A.RetailerID
This is less simple but offers more flexibility:
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

June 24, 2008 at 8:30 am

#833342
RE: Create Temporary Tables Dynamically in cursor loop

elmerbotha (6/23/2008)
When I get the resultset a second, or nth time, I obviously need to take into account the 'thrown away values' so they are not returned again, but rather...
^{“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

June 23, 2008 at 8:38 am

#832744

Viewing 15 posts - 9,826 through 9,840 (of 10,143 total)