malleswarareddy_m (8/11/2010)I guessed correctly as 4.but i executed the script it is throwing error in the last statement.because group by is not there in the last statement.
Based on visual inspection of the code, you appear to be right about the missing GROUP BY. I'm glad I overlooked that when answering! Luckily, there is no generic "error" answer option, so people will hopefully realise that this is an unintended mistake.
after throung error i put group by and executed it is showing zero rows.
Did you load the table with data? Millions of rows is not necessary, but at least one in every partition is.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis