OVER clause enhancement request - DISTINCT clause for aggregate functions
SQL Server 2005 introduced only partial support for the OVER clause. It is our strong belief that a more complete implementation of the OVER clause should be prioritized highly in consideration for future enhancements in SQL Server. A more complete implementation of the OVER clause can help in solving many common business problems with simpler, more intuitive, and faster solutions than available today and also substantially reduce the need for cursors.
The following paper details the feature enhancement requests:
The current feedback item addresses the OVER clause enhancement request - DISTINCT clause for aggregate functions, discussed in the paper in section 4 item vi.
Upvotes: 577<=-=Dec 13 2007 3:33PM=-=>
Thanks a lot for your feedback… and the strong voting support for this feature. Indeed, as you know, I am very much in favor of extending our functionality in this area. For a variety of reasons we did not get this into SQL Server 2008, but we are certainly looking into it for a future release.
Keep the votes and comments coming…
PS: My apologies for the late official reply…<=-=Mar 10 2009 4:37PM=-=>
I am a big fan of the ranking functions and partition by clause. I used them extensively. It was a fantastic inclusion in 2005. However, I can’t tell the number of times I wished DISTINCT worked with the count function. I can’t believe it wasn’t included in 2008!! So I’ve added my vote to get this in ASAP.<=-=Sep 15 2009 7:08AM=-=>
yes this would be very helpful. I’m sitting here frustrated over my inability to do this.<=-=Dec 14 2009 5:53PM=-=>
Count(DISTINCT Col1) OVER (PARTITION BY Col2) would solve my problem perfectly! I need to know if the number of items is different than the number of distinct items. Solving this another way will be tricky.<=-=Dec 14 2009 6:05PM=-=>
If we could nest ranking functions this would do the same thing (of course, we can’t so this code doesn’t work):
Rnk = Max(Dense_Rank() OVER (PARTITION BY X ORDER BY Y)) OVER (PARTITION BY X)
SELECT ‘a’ x, 1 y
UNION ALL SELECT ‘a’, 1
UNION ALL SELECT ‘a’, 2
UNION ALL SELECT ‘b’, 1
UNION ALL SELECT ‘b’, 1
I use the over() clause quite a bit and it’s been very useful, however only now am I really wanting more functionality from it, so figured I’d bump this thread. I’m good with workarounds, and not entirely sure I, “need” additional functionality, however it would surely lend itself to more elegant solutions.
<=-=Nov 3 2011 12:38PM=-=>
- sql 2008 r2, mainly using over() for data warehousing, or tweaky ad hoc reports, DBA, New Belgium Brewery
It would be nice to be able to use the result of an OVER clause in a where clause too. I’m always having to make wrappers just to filter the result of an OVER clause.<=-=Oct 31 2012 10:23AM=-=>
Say you are reporting on a price comparison engine.
In a given time period, you want a count of how many users clicked through to each website whose prices you compare.
On the assumption that users make at most one purchase per website per day, you want to dedupe the cases where users click through many times.
The Referrals table contains one row per click-through.
To get this deduped count for yesterday, you should be able to write
COUNT OVER (PARTITION BY Website) AS DedupedUserCount
ReferralTime >= ‘2012-10-30’ AND
ReferralTime < ‘2012-10-31’
This fails with the disappointing messageMsg 10759, Level 15, State 1, Line 11 Use of DISTINCT is not allowed with the OVER clause.
Plamen Ratchev provides a workaround (see Workarounds) using the DENSE_RANK function in a subquery and the MAX function.
But we should be able to do it as declaratively as the SQL grammar allows!<=-=Jan 10 2013 12:47PM=-=>
I really hope this feature is a part of SQL Server 2012 because I just tried to use it in 2008 and came here to find it’s not supported. I’m using something like the code below to get the result.
SELECT COUNT AS DedupedUserCount
FROM Referrals AS R
GROUP BY R.Website
HAVING ReferralTime >= ‘2012-10-30’ AND ReferralTime < ‘2012-10-31’;
It’s really frustrating that this feature is not included in SQL Server 2012 also !<=-=Aug 14 2015 1:40PM=-=>
I know this is a much newer comment on this but we are currently on 2008 R2 for SQL Server due to business constraints and having this functionality would make solving my current problem so incredibly easy.<=-=Nov 30 2015 2:39AM=-=>
You should fix this. It is not working in SQL Server 2014 also. This limitation was reported almost 9 years ago.<=-=Jun 29 2016 5:51AM=-=>
This is many useful place being able to do a Select count distinct x Over… would make things so easy. Typical workarounds for this lack of functionality are counter intuitive.
Being unable to just add a DISTINCT to the query was definitely a nasty shock.