ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,961 through 6,975 (of 10,143 total)

RE: Find Similar rows and add totals together.

leroy-1092048 (12/1/2010)
...
update the row where E = Min(E) and set D with the value of SUM(D)
delete all rows not equal to minID's
...
So...how will you identify...
^{“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

December 2, 2010 at 2:42 am

#1257765
RE: Today's Random Word!

Kit G (12/1/2010)
Anniversary (as in my 20th wedding anniversary) :w00t:
Hearty congratulations Mrs G!
^{“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

December 1, 2010 at 8:40 am

#1257427
RE: Are the posted questions getting worse?

WayneS (12/1/2010)
Chris Morris-439714 (12/1/2010)
WayneS (12/1/2010)
Chris Morris-439714 (12/1/2010)
WayneS (11/30/2010)
To all the Threadizens that have presented - how have you incorporated this public speaking into your resume? Examples would be great!
Good Lord...
^{“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

December 1, 2010 at 8:28 am

#1257408
RE: Find Similar rows and add totals together.

Looks like you were on the right track at the start - does this help?
;WITH RowPicker AS (
SELECT A, B, C, D, E,
Total = SUM(D) OVER (PARTITION BY A,...
^{“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

December 1, 2010 at 8:15 am

#1257391
RE: Today's Random Word!

Ray K (12/1/2010)
lime
Scurvy
^{“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

December 1, 2010 at 7:12 am

#1257335
RE: Are the posted questions getting worse?

WayneS (12/1/2010)
Chris Morris-439714 (12/1/2010)
WayneS (11/30/2010)
To all the Threadizens that have presented - how have you incorporated this public speaking into your resume? Examples would be great!
Good Lord Wayne you're morphing...
^{“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

December 1, 2010 at 7:10 am

#1257333
RE: self join

vinoth-750003 (12/1/2010)
hi
Thanks for the response.
Our problem resolved.
by
VinothKumar.K
I'd recommend that you hang around for a day or two. Often a piece of klunky code like this will be superceded by...
^{“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

December 1, 2010 at 6:36 am

#1257303
RE: self join

This is a horrible piece of code but it works:
DROP table #Emp
create table #Emp
(
empid int primary key,
empname varchar(50),
managerid int
)
INSERT INTO #Emp (empid, empname, managerid)
SELECT 1000, 'mani', 0 UNION ALL
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

December 1, 2010 at 5:53 am

#1257271
RE: Delete duplicate records in a table

chitturiii (12/1/2010)
Hi,
Can you please suggest me the best query to delete the duplicate records in a table with large number of records.
Thanks in advance.
--Chandra
You may have to delete in batches...
^{“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

December 1, 2010 at 2:59 am

#1257218
RE: Today's Random Word!

Here in Middle England the sun is shining through the office window. Horizontally of course but it's there and folks are brighter for it. Outside it's about 0C with a...
^{“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

December 1, 2010 at 2:29 am

#1257209
RE: Are the posted questions getting worse?

Jeff Moden (11/30/2010)
Grant Fritchey (11/30/2010)
Jeff Moden (11/30/2010)
Changing subjects, have you seen today's featured script? It comes under the category of "Are the featured scripts getting worse"? I try...
^{“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

December 1, 2010 at 2:25 am

#1257205
RE: Are the posted questions getting worse?

WayneS (11/30/2010)
To all the Threadizens that have presented - how have you incorporated this public speaking into your resume? Examples would be great!
Good Lord Wayne you're morphing into Karthik :unsure:
^{“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

December 1, 2010 at 2:21 am

#1257204
RE: Are the posted questions getting worse?

GilaMonster (11/30/2010)
GSquared (11/30/2010)
If it were written:
SELECT ..
FROM T1
inner join T2
on T1.a >= T2.b
inner join T3
on T1.a <= T3.c;
Would you still question it? It's the same...
^{“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

December 1, 2010 at 2:20 am

#1257203
RE: How to know which join will be used first & so on?

Query1, Query2 and Query3 are functionally the same. If you run these three queries and look at the actual plans for each one, they are very likely to be identical....
^{“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

November 30, 2010 at 9:01 am

#1256839
RE: How to know which join will be used first & so on?

pravin.sawant99 (11/30/2010)
Please can you tell
1) which table will first scan by sql server then which one..
2) which join will be considered first.
3) whether order of join causes query performance?
1) Check...
^{“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

November 30, 2010 at 8:36 am

#1256820

Viewing 15 posts - 6,961 through 6,975 (of 10,143 total)