Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Window Functions and Group By Auto-fill

tonywaddletonywaddle Posts: 16 Bronze 1
edited December 9, 2016 9:48AM in SQL Prompt
Hi,

I've just seen that Window functions can be incorrectly populated in the 'group by' portion of a SQL statement when the partition portion of the function is populated.

For instance the following group by was auto-filled, and produces an invalid statement:
SELECT  Attribute,
             Value,
             COUNT(*) OVER (PARTITION BY a.Attribute),
             COUNT(*)
FROM    #Attribute a
            INNER JOIN #Value v ON v.Col = a.col
                               AND a.a = v.Sec
GROUP BY COUNT(*) OVER (PARTITION BY a.Attribute),
            a.Attribute,
            v.Value

When trying to reproduce this error, the auto-fill started to only add the 'value' column (which is also incorrect - it should populate attribute and value) but using Refresh Suggestions command reverted it back to populating the window function as above.

Thanks,
Tony

Comments

Sign In or Register to comment.