ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,521 through 8,535 (of 10,143 total)

RE: query

Sure:
DROP TABLE #T1
CREATE TABLE #T1 (PersonId VARCHAR(2), OID VARCHAR(2))
INSERT INTO #T1 (PersonId, OID)
SELECT 'P1', 'O1' UNION ALL
SELECT 'P2', 'O7' UNION ALL
SELECT 'P3', 'O1'
SELECT a.PersonId, (SELECT OID FROM #T1 WHERE PersonId...
^{“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

April 9, 2009 at 6:46 am

#974986
RE: Trimming unwanted characters from string

Don't forget a tally-table version!
DECLARE @Untrimmed varchar(50), @Trimmed varchar(50)
SET @Untrimmed = 'HaveNow!/7896-7'
SET @Untrimmed = 'HaveNow'
--
SELECT @Trimmed = LEFT(@Untrimmed, (SELECT ISNULL(MIN(n.number)-1, LEN(@Untrimmed))
FROM Numbers n
WHERE n.number <= LEN(@Untrimmed)
AND NOT ascii(lower(substring(@Untrimmed, n.number,1)))...
^{“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

April 9, 2009 at 6:23 am

#974968
RE: Trimming unwanted characters from string

karthur (4/9/2009)
I have a column with 'name/dept' or 'name-dept', such as 'jones/1234' or 'jones-1234'.
Is there a function or functions I can use in SQL Server 2005 to give me just...
^{“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

April 9, 2009 at 5:16 am

#974930
RE: sequence in a range

No worries Allan, though I think it's Adi who deserves the credit.
Have a read of this when you've got a few minutes.
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

April 9, 2009 at 4:29 am

#974918
RE: cursor goes in to infinit loop.........

The BEGIN is in the wrong place. Here's the example from BOL:
DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
...
^{“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

April 9, 2009 at 4:24 am

#974916
RE: sequence in a range

Do you mean this?
DROP TABLE #GroupMembers
CREATE TABLE #GroupMembers (
Group_member_id INT,
Member_name VARCHAR(10),
Group_name VARCHAR(3),
[Order] INT)
...
^{“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

April 9, 2009 at 3:12 am

#974900
RE: Multi Column Sort

john.arnott (4/8/2009)
OK. If the puzzle's rules specify that the given data always has increasing values in the respective columns of each row, so be it. Does that affect...
^{“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

April 9, 2009 at 2:19 am

#974880
RE: Multi Column Sort

RBarryYoung (4/8/2009)
Peso (4/8/2009)
I agree with most points, but I strongly disagree with creating sample data that fulfills OP's wanted output. I don't believe in some divine intervention that sample 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

April 8, 2009 at 3:09 pm

#974737
RE: Multi Column Sort

RBarryYoung (4/8/2009)
Yeah, my data follows the rules, but plays havoc with any simple distribution assumptions.
😀
No fair :sick: it's only 25% populated with numbers! If that!
^{“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

April 8, 2009 at 10:17 am

#974481
RE: Multi Column Sort

Peso (4/8/2009)
Ramesh (4/8/2009)
See these results, the row 5 should be followed after row 4 as the col2 value of row 5 is higher than that of row 4.
-- Actual Results
ID...
^{“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

April 8, 2009 at 8:26 am

#974350
RE: Multi Column Sort

Peso (4/8/2009)
For Number < 40000 I get these results
Ramesh' Version : 2683 ms
Peso : 1546 ms
Here's a randomised set of 15 rows, Peso - wanna give them both a 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

April 8, 2009 at 8:07 am

#974321
RE: Multi Column Sort

Peso (4/8/2009)
Using Ramesh's test cases, I get these timings:
Ramesh' Version : 526 ms
ChrisM' Version 3 ("condensed" version 2) : 23480 ms
Peso : 236 ms
Ooookaaaaayyyy proof of the pudding...run against randomised...
^{“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

April 8, 2009 at 7:53 am

#974294
RE: Multi Column Sort

36376 ms? What are you running this test on, Ramesh - a twin-floppy 80's luggable? 😀
876 ms is awesome:cool:
^{“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

April 8, 2009 at 7:24 am

#974257
RE: Multi Column Sort

Very elegant! Makes my CASEy stuff look like a dustbin full of spaghetti!
Who's gonna test with 40,000 rows then? 😀
^{“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

April 8, 2009 at 6:24 am

#974205
RE: Query Performance

GSquared is right - you need those SELECT (SELECT constructs in there as he describes.
Also, the final query is missing SELECT [columnlist].
^{“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

April 7, 2009 at 3:05 pm

#973836

Viewing 15 posts - 8,521 through 8,535 (of 10,143 total)