What are the challenges you face when working across database platforms? Take the survey
Options

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

  • Options
    James RJames R Posts: 104 Silver 4
    Hi Tony,

    Thanks for your post! We've managed to reproduce this issue here and are working on a fix now.

    Thanks,
    James
    Software Engineer
    Redgate Software
Sign In or Register to comment.