How can we help you today? How can we help you today?

GROUP BY suggestion incorrect for query containing FIRST_VALUE

CREATE TABLE #deleteme (
	id INT
    ,value1 INT
)

SELECT
	id
	,FIRST_VALUE(id) OVER (ORDER by value1 DESC) AS foo
FROM #deleteme
GROUP BY FIRST_VALUE(id) OVER (ORDER BY value1 DESC), id -- what SQL Prompt suggests
	
SELECT
	id
	,FIRST_VALUE(id) OVER (ORDER by value1 DESC) AS foo
FROM #deleteme
GROUP BY value1, id -- what it should suggest

Pretty simple, really. What SQL Prompt suggests is invalid TSQL (can't have windowing function in a GROUP BY).

-steve

spthorn
0

Comments

5 comments

Sort by
  • DustinM
    Hi Steve,

    Thank you for bringing this to our attention, I was able to replicate this on my end and will escalate it to the dev team.

    Best,

    Dustin
    DustinM
    0
  • DustinM
    Hi Steve,

    I wanted to follow up on this post our dev team has added this to their board to investigate.

    Best,

    Dustin
    DustinM
    0
  • spthorn
    Sweet. Thanks for tracking this.
    spthorn
    0
  • DustinM
    Hello, 

    I wanted to follow up on this forum post, this bug has been resolved in the SQL Prompt v10.13.6 release.

    Best, 

    Dustin
    DustinM
    0
  • spthorn
    Thanks, Dustin. Always a RedGate fan.
    spthorn
    0

Add comment

Please sign in to leave a comment.