Forum Replies Created
Viewing 15 posts - 361 through 375 (of 1,439 total)
-
RE: Data Type
Dave62 (5/10/2013)
Nice question! I had to guess the correct answer. According to the script below the correct answer may not be so simple.-- Code from the QotD
select *...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
RE: Simple derived table with multiple rows
Two possibilities, I'm sure there are more
SELECT 1 AS colUNION ALL
SELECT 2
UNION ALL
SELECT 3
SELECT colFROM (VALUES (1), (2), (3)) x(col)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Recursive SQL
I get 3 rows not 4, maybe I'm missing something.
DECLARE @Start INT = 395;WITH Recur AS (
SELECT [ColA], [ColB], [ColC], [ColD], [ColE], [ColF]
FROM [dbo].[tmpTable]
WHERE [ColA] = @Start
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Query Help
kapil_kk (5/9/2013)
Mark-101232 (5/9/2013)
WITH CTE AS (SELECT ID,VoucherNo,Status,VoucherType,
ROW_NUMBER() OVER(ORDER BY ID) AS rn1,
ROW_NUMBER() OVER(PARTITION BY Status ORDER...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Query Help
WITH CTE AS (SELECT ID,VoucherNo,Status,VoucherType,
ROW_NUMBER() OVER(ORDER BY ID) AS rn1,
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID)...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: XML.Value when it has attributes?
Quick 'n dirty solution
SELECT *,RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Get Latest Revision of Attribute Based on Date
Works but I suspect there's a better way (using CROSS APPLY?)
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col1...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Update Column after Alter Table ADD column
Depending on your requirements, you can do this in one statement
ALTER TABLE Schema1.Table1 ADD Column1 int NOT NULL DEFAULT(-1)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Add 2 values from 1 table
Run the same query without the aggregate (i.e. without the SUM) as below, you'll see that each row has either a zero or a one
calculated for it. All we...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Add 2 values from 1 table
Select sum(case when Loadflag='update' then 1 else 0 end) as 'Records Updated',sum(case when Loadflag is null then 1 else 0 end) as 'New...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: XML Question (?)
Not sure of your table structure, but this give the correct results
DECLARE @tbl TABLE(Code1 VARCHAR(10), Code2 VARCHAR(10), Field1 INT, Field2 INT, Field3 INT)INSERT INTO @tbl(Code1,Code2,Field1,Field2,Field3)
VALUES ('Rec1_Code1','Rec1_Code2',11,22,33),
('Rec2_Code1','Rec2_Code2',1111,2222,3333);
SELECT
Code1...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: T-SQL Greatest function workarounds - timing tests
Evil Kraig F (4/30/2013)
Mark-101232 (4/30/2013)
Results from 2012 SP1Cascade Test131824601220
CASE Test123823501150
Values Test6011016503
Appreciate the time Mark. Would you be willing to rerun with the harness in the fourth post? After...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Parent/Child and Recursion
Try this
DECLARE @t TABLE(Parent VARCHAR(10), Child VARCHAR(10))
INSERT INTO @t(Parent,Child)
VALUES
('300001','110081'),
('300001','102157'),
('300001','102158'),
('300001','102159'),
('110081','101000'),
('110081','101504'),
('110081','102129');
WITH Recur AS (
SELECT Parent,Child
FROM @t t
WHERE NOT EXISTS(SELECT * FROM @t t2 WHERE t2.Child=t.Parent)
UNION ALL
SELECT r.Parent,t.Child
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Use of rownum function in a select
Use
ROW_NUMBER() OVER(PARTITION BY OM.ord_route ORDER BY ...)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: T-SQL Greatest function workarounds - timing tests
Results from 2012 SP1
Cascade Test131824601220
CASE Test123823501150
Values Test6011016503
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 361 through 375 (of 1,439 total)