GROUP BY suggestion incorrect for query containing FIRST_VALUE

spthorn

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

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

Hi Steve,

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

Best,

Dustin 

spthorn

Sweet. Thanks for tracking this.