December 16, 2015 at 9:41 am
I need to make these selections
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (1) ROWS ONLY
) AS s
UNION
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (2) ROWS ONLY
) AS s
UNION
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (3) ROWS ONLY
) AS s
UNION
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (4) ROWS ONLY
) AS s
UNION
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (5) ROWS ONLY
) AS s
Is there a way I can a single statement like this, with the table @tb_fetch as a single column table parameter having the values 1 to 5 above.
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (0) ROWS
FETCH NEXT (SELECT * FROM @tb_fetch) ROWS ONLY
) AS s
December 16, 2015 at 10:11 am
Not tested, so probably riddled with syntax errors, but this should get you started:
WITH Nos(m) AS (SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT STDEVP(basis),
STDEVP(quote_1)
FROM Nos
CROSS APPLY
(
SELECT * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM @tb_basis
ORDER BY n
OFFSET (Nos.n) ROWS
FETCH NEXT (SELECT * FROM @tb_fetch) ROWS ONLY
) AS s
John
December 16, 2015 at 10:14 am
It looks like you're trying to recreate the windowed functions that already exist in T-SQL. Try the following instead:
SELECT STDEVP(basis) OVER(ORDER BY basis ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
STDEVP(quote1) OVER(ORDER BY basis ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @tb_basis
ORDER BY basis
OFFSET (0) ROWS
FETCH NEXT (5) ROWS ONLY
The "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is not strictly necessary. T-SQL will use the similar "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" by default. I have specified it, because (1) I wanted to make clear the window that I was using, and (2) ROWS will sometimes perform better than RANGE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 16, 2015 at 10:27 am
Thanks A LOT!! This is exactly what I was looking for!
Thanks again 🙂
December 16, 2015 at 10:28 am
John more-or-less beat me to it and drews solution is probably better but here's what I came up with. Using an inline table valued function you could do this:
USE tempdb
GO
-- Sample Data
IF OBJECT_ID('tempdb..tb_basis') IS NOT NULL DROP TABLE tb_basis;
CREATE TABLE tb_basis(basis int not null, quote_1 int not null);
INSERT tb_basis
SELECT TOP (10) ABS(CHECKSUM(newid())%10)*1., ABS(CHECKSUM(newid())%100)+1
FROM sys.all_columns;
--Function
IF OBJECT_ID('tempdb..sbasis') IS NOT NULL DROP FUNCTION sbasis;
GO
CREATE FUNCTION sbasis (@rows int)
RETURNS TABLE AS RETURN
SELECT basis = STDEVP(basis), quote_1 = STDEVP(quote_1)
FROM
(
SELECT TOP(@rows) * ,ROW_NUMBER() OVER (ORDER BY basis) AS n
FROM tb_basis tb
ORDER BY n
) x
GO
-- Use
WITH Nums(N) AS (SELECT TOP(5) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
SELECT basis, quote_1
FROM Nums
CROSS APPLY sbasis(N);
-- Itzik Ben-Gan 2001
December 16, 2015 at 10:29 am
Thanks, but the solution provided by Drew is scalable, which is what I wanted. Thanks any way
December 16, 2015 at 10:32 am
stevennjuki (12/16/2015)
Thanks, but the solution provided by Drew is scalable, which is what I wanted. Thanks any way
Cool. On a side note for future reference about UNION... UNION ALL pretty much always outperforms UNION (AKA UNION DISTINCT). Your original query would return the same results with UNION ALL and would certainly perform better. 😉
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply