Count(Distinct ColumnName) Over Bug - by Eric Wisdahl

Status : 

 


21
0
Sign in
to vote
ID 350940 Comments
Status Active Workarounds
Type Bug Repros 4
Opened 6/13/2008 11:20:53 AM
Access Restriction Public
Primary Feedback Item 254393

Description

I'm not sure if this is actually a bug or not, but from the BOL there does not appear to be any documentation indicating that the following query contains any incorrect syntax.  

NOTE: this query makes no real logical sense, but it is easier than writing out the full description of what I was looking at when I ran into this problem.  

SELECT 
 [ManagerID]
 ,Count(Distinct [ContactID]) OVER (PARTITION BY [ManagerID]) as MyTestCount
FROM 
 [AdventureWorks].[HumanResources].[Employee];

As an aside, I realize that I can instead use a derived table to gather the information using a dense_rank() and then max().  I also realize that I could use a group by statement instead of the OVER.  However, Both of these feel like simple hacks that work around a bug to me.  What would keep the engine from properly parsing the distinct?
Sign in to post a comment.
Posted by Microsoft on 6/16/2008 at 2:24 PM
Btw, I want to encourage you to post feedback on the Books Online topics so we can correct it at the documentation level. I will also follow-up on this.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 6/16/2008 at 2:23 PM
Hi,
Thank you very much for your feedback regarding the lack of DISTINCT support with window functions. This is a known limitation in the product that we will look into for a future version of SQL Server. You can see the suggestion item below and vote on it:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

We will not fix this in SQL Server 2005. So I will be resolving this bug as a duplicate of the above mentioned suggestion item.

--
Umachandar, SQL Programmability Team