Bruno Wolff III [EMAIL PROTECTED] writes:
The case I was thinking of were datatypes without a defined ordering
where max and min wouldn't be usable. But if GROUP BY was going to
changed to allow any columns if the primary key was used in the GROUP
BY clause, I can't see any use for those
On 14 Mar 2005, Greg Stark wrote:
select distinct on (x) x,y,z
order by x,y,z
You can do the equivalent:
select x, first(y), first(z)
order by x,y,z
group by x
But you can also handle the more general case like:
select x, first(y), first(z), avg(a), sum(s)
order by x,y,z
Dennis Bjorklund [EMAIL PROTECTED] 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
On 14 Mar 2005, Greg Stark wrote:
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
Dennis Bjorklund [EMAIL PROTECTED] writes:
They are aggregate functions, the avg() is a window aggregate function
according to the standard. It runs over all values in the same partition.
-- albeit functions that use data from other records other
than the one being output.
Yes, and
On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote:
Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?
In Sybase:
1 select 2 as id, max(myfield) from mytable where 2=1
2 go
id
--- --
2
select 1 from tab having 1=1;
returns 2 rows
I'm curious whats in those two rows... {{1} {1}} ?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote:
Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?
Mysql treats ungrouped columns as an assertion that those columns will all be
equal for the group and it can pick an arbitrary
On Sun, Mar 13, 2005 at 23:24:18 -0500,
Greg Stark [EMAIL PROTECTED] wrote:
I've noticed quite frequently scenarios where this idiom would be very handy.
I usually either end up rewriting the query to have nested subqueries so I can
push the grouping into the subquery. This doesn't always
Bruno Wolff III [EMAIL PROTECTED] writes:
If someone did a naive implementation of first() and last() aggregates
for 8.1, is that something that would likely be accepted?
You mean like this?
CREATE FUNCTION first_accum(anyelement,anyelement) RETURNS anyelement as
'select coalesce($1,$2)'
On Mon, Mar 14, 2005 at 00:35:32 -0500,
Greg Stark [EMAIL PROTECTED] wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
If someone did a naive implementation of first() and last() aggregates
for 8.1, is that something that would likely be accepted?
You mean like this?
CREATE
Greg Stark [EMAIL PROTECTED] writes:
On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote:
Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?
Mysql treats ungrouped columns as an assertion that those columns will all be
equal for the
Bruno Wolff III [EMAIL PROTECTED] writes:
If someone did a naive implementation of first() and last() aggregates
for 8.1, is that something that would likely be accepted?
For the purpose that Greg is suggesting, these would have no advantage
over min() or max() --- since the system wouldn't
On Mon, Mar 14, 2005 at 01:52:59 -0500,
Tom Lane [EMAIL PROTECTED] wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
If someone did a naive implementation of first() and last() aggregates
for 8.1, is that something that would likely be accepted?
For the purpose that Greg is suggesting,
14 matches
Mail list logo
