Re: [BUGS] We are not following the spec for HAVING without GROUP

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> writes:

> The standard (sql2003) have what is called windows where one can do these
> things and much more.

OLAP functions would be very nice. But they're not the same thing. In fact
that's precisely *why* they would be really nice. They allow you to do things
that are not feasible to do with just plain old GROUP BY.

But I stared at this spec for a while and it seemed like implementing it would
be pretty hard. Like, I don't see any way to implement your query below
without sorting every record in the group repeatedly for every record. That
would be a lot of sorts.

> SELECT ROW_NUMBER() OVER bar AS num,
> x,
> avg(a) OVER bar,
> sum (a) OVER bar
> FROM foo
> WINDOW bar AS PARTITION BY x ORDER BY x, y, z;

Note that as you said, this returns just as many records as are in the
original table. The OLAP functions here are just regular functions, not
aggregate functions -- albeit functions that use data from other records other
than the one being output.

DISTINCT ON is just another spelling for GROUP BY, it always outputs only one
record per group.

> ps2. I'd love to read a book that discusses the sql2003 (or even sql99)
> that explain features, give examples, and so on. But i guess the market
> don't want books that explain things that no database have implemented yet
> (Oracle have window functions but i've never used that).

DB2 has them too. Check out this DB2 documentation, it might be more helpful
than the specs.

http://publib.boulder.ibm.com/infocenter/rb63help/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg35.htm

--
greg