How is SQL Prompt helping your team? Share your experience.

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

Answers

Sign In or Register to comment.