Distinct aggregation and Sql99 Olap functions

Hi,
I noticed that SS2K5 doesn't support distinct aggregation with sql99 olap
syntax. e.g.
select country, count(distinct userid) over (partition by country), ... from
...
I know this is also not supported by DB2/TeraData, but supported by
Oracle. Conceptually, I don't see an obvious reason why distinct aggregation
shouldn't not be supported by SS2k5. Is this just strict interpretation of
the Sql99 standard or is there a more fundamental reason? Any insight would
be greatly appreciated.I can imagine that it's just an implementation issue that would require more
development resources.
There are other aspects of the OVER clause which I find more important that
were not implemented yet (ORDER BY, ROWS/RANGE clauses for window based
aggregates).
BTW, you're particular request can be satisfied with the following
alternative:
USE Northwind;
WITH C AS
(
SELECT ShipCountry,
DENSE_RANK() OVER(PARTITION BY ShipCountry ORDER BY CustomerID) AS DRnk
FROM dbo.Orders
)
SELECT ShipCountry, MAX(DRnk) OVER(PARTITION BY ShipCountry) AS DCntCust
FROM C;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:9416446C-F097-416E-BA57-7AA538B46D8F@.microsoft.com...
> Hi,
> I noticed that SS2K5 doesn't support distinct aggregation with sql99
> olap
> syntax. e.g.
> select country, count(distinct userid) over (partition by country), ...
> from
> ...
> I know this is also not supported by DB2/TeraData, but supported by
> Oracle. Conceptually, I don't see an obvious reason why distinct
> aggregation
> shouldn't not be supported by SS2k5. Is this just strict interpretation of
> the Sql99 standard or is there a more fundamental reason? Any insight
> would
> be greatly appreciated.|||I have a feeling that you will ask, what if I want a SUM(DISTINCT)...?
USE pubs;
WITH C AS
(
SELECT stor_id, qty,
ROW_NUMBER() OVER(PARTITION BY stor_id, qty
ORDER BY stor_id, qty) AS rn
FROM dbo.sales
)
SELECT stor_id,
SUM(CASE WHEN rn = 1 THEN qty END) OVER(PARTITION BY stor_id) AS dsumqty
FROM C;
;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ug7tle60FHA.3376@.TK2MSFTNGP14.phx.gbl...
>I can imagine that it's just an implementation issue that would require
>more development resources.
> There are other aspects of the OVER clause which I find more important
> that were not implemented yet (ORDER BY, ROWS/RANGE clauses for window
> based aggregates).
> BTW, you're particular request can be satisfied with the following
> alternative:
> USE Northwind;
> WITH C AS
> (
> SELECT ShipCountry,
> DENSE_RANK() OVER(PARTITION BY ShipCountry ORDER BY CustomerID) AS DRnk
> FROM dbo.Orders
> )
> SELECT ShipCountry, MAX(DRnk) OVER(PARTITION BY ShipCountry) AS DCntCust
> FROM C;
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:9416446C-F097-416E-BA57-7AA538B46D8F@.microsoft.com...
>