Query to return 1 or unique value

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Hello,

    I am using SQL 2k8 R2. My colleague would like query that will return unique value if all the supplied (or column) values are same else return 1.

    One way I write the query as below:

    SELECT CASE WHEN COUNT(DISTINCT a) = 1

    THEN SUM(DISTINCT a) / COUNT(DISTINCT a)

    ELSE 1

    END

    FROM ( SELECT 220

    UNION ALL

    SELECT 220

    UNION ALL

    SELECT 220

    UNION ALL

    SELECT 220

    ) a ( a )

    Above pattern will be used in original query which already has "Group By" clause for grouping of data (sum, avg etc..).

    Is there any alternate method for the same that will give best result?

    Thanks

  • ChrisM@Work

    SSC Guru

    Points: 186107

    SELECT TOP 1

    CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END

    FROM (

    SELECT a,

    CountInGroup = COUNT(*),

    SumGroup = SUM(a),

    GroupCount = COUNT(*) OVER(PARTITION BY @@spid)

    FROM ( SELECT 220 UNION ALL

    SELECT 220 UNION ALL

    SELECT 220 UNION ALL

    SELECT 221 UNION ALL

    SELECT 220

    ) a ( a )

    GROUP BY a

    ) d

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Cadavre

    SSC-Forever

    Points: 41690

    Is that really any better Chris? I make it, at best the same or slightly worse.

    Test 1

    SELECT *

    INTO #test

    FROM (SELECT 221

    UNION ALL

    SELECT TOP 1000000 220

    FROM master.sys.all_columns a, master.sys.all_columns b,

    master.sys.all_columns c)a(a);

    PRINT 'CHRIS';

    PRINT REPLICATE('-',80);

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    SELECT TOP 1

    CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END

    FROM (

    SELECT a,

    CountInGroup = COUNT(*),

    SumGroup = SUM(a),

    GroupCount = COUNT(*) OVER(PARTITION BY @@spid)

    FROM #test a

    GROUP BY a

    ) d;

    SET STATISTICS TIME OFF;

    SET STATISTICS IO OFF;

    PRINT REPLICATE('-',80);

    PRINT 'HARDIK';

    PRINT REPLICATE('-',80);

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    SELECT CASE WHEN COUNT(DISTINCT a) = 1

    THEN SUM(DISTINCT a) / COUNT(DISTINCT a)

    ELSE 1

    END

    FROM #test a;

    SET STATISTICS TIME OFF;

    SET STATISTICS IO OFF;

    CHRIS

    --------------------------------------------------------------------------------

    Table 'Worktable'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#test_______________________________________________________________________________________________________________000000000028'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 313 ms, elapsed time = 82 ms.

    --------------------------------------------------------------------------------

    HARDIK

    --------------------------------------------------------------------------------

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#test_______________________________________________________________________________________________________________000000000028'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 187 ms, elapsed time = 47 ms.

    Test 2

    SELECT *

    INTO #test

    FROM (--SELECT 221

    --UNION ALL

    SELECT TOP 1000000 220

    FROM master.sys.all_columns a, master.sys.all_columns b,

    master.sys.all_columns c)a(a);

    PRINT 'CHRIS';

    PRINT REPLICATE('-',80);

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    SELECT TOP 1

    CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END

    FROM (

    SELECT a,

    CountInGroup = COUNT(*),

    SumGroup = SUM(a),

    GroupCount = COUNT(*) OVER(PARTITION BY @@spid)

    FROM #test a

    GROUP BY a

    ) d;

    SET STATISTICS TIME OFF;

    SET STATISTICS IO OFF;

    PRINT REPLICATE('-',80);

    PRINT 'HARDIK';

    PRINT REPLICATE('-',80);

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    SELECT CASE WHEN COUNT(DISTINCT a) = 1

    THEN SUM(DISTINCT a) / COUNT(DISTINCT a)

    ELSE 1

    END

    FROM #test a;

    SET STATISTICS TIME OFF;

    SET STATISTICS IO OFF;

    CHRIS

    --------------------------------------------------------------------------------

    Table 'Worktable'. Scan count 3, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#test_______________________________________________________________________________________________________________000000000029'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 250 ms, elapsed time = 66 ms.

    --------------------------------------------------------------------------------

    HARDIK

    --------------------------------------------------------------------------------

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#test_______________________________________________________________________________________________________________000000000029'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 142 ms, elapsed time = 46 ms.

    The results are similar even if we add an index.

    With no index, this is worse than both of the solutions above: -

    SELECT CASE WHEN a = b THEN a ELSE 1 END

    FROM (SELECT MAX(a), MIN(a)

    FROM #test) b(a,b);

    --------------------------------------------------------------------------------

    TEST

    --------------------------------------------------------------------------------

    Table '#test_______________________________________________________________________________________________________________000000000035'. Scan count 1, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 282 ms, elapsed time = 277 ms.

    However, if we add an index.

    CREATE NONCLUSTERED INDEX nc_idx_a_test ON #test (a);

    Then run all three again.

    CHRIS

    --------------------------------------------------------------------------------

    Table 'Worktable'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 1, logical reads 1863, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 219 ms, elapsed time = 225 ms.

    --------------------------------------------------------------------------------

    HARDIK

    --------------------------------------------------------------------------------

    Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 1, logical reads 1863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 156 ms, elapsed time = 154 ms.

    --------------------------------------------------------------------------------

    TEST

    --------------------------------------------------------------------------------

    Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Cadavre (6/13/2012)


    Is that really any better Chris? I make it, at best the same or slightly worse...

    You're absolutely right.

    Even with the window function removed, the original code is faster. It simply looks like it might be inefficient.

    Never underestimate the value of a good test.

    Cadavre, you're clearly bored! πŸ˜›

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Thanks ChrisM@Work , Cadavre

    I am using original query which I have shared πŸ™‚

    Thanks

  • Cadavre

    SSC-Forever

    Points: 41690

    ChrisM@Work (6/13/2012)


    You're absolutely right.

    Even with the window function removed, the original code is faster. It simply looks like it might be inefficient.

    Never underestimate the value of a good test.

    Cadavre, you're clearly bored! πŸ˜›

    I certainly was this morning πŸ˜‰


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply