Using Multi-result subquery in expression

  • 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

  • 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

  • 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

  • Thanks A LOT!! This is exactly what I was looking for!

    Thanks again 🙂

  • 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);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks, but the solution provided by Drew is scalable, which is what I wanted. Thanks any way

  • 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. 😉

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- 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