Re: Ordering with GROUPs

From: "Julian Scarfe" <julian(dot)scarfe(at)ntlworld(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Ordering with GROUPs
Date: 2002-08-19 08:01:02
Message-ID: 06dc01c24756$9008f1a0$0500a8c0@Wilbur
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> ISTM the problem here is the lack of any ordering operator for POINT,
> which defeats GROUP BY, *plus* the lack of any aggregate you might use
> for an aggregate-based solution. This is not really a language failing
> but a problem with an impoverished datatype.

Yes, I agree completely. If I were doing this again from scratch I'd be
using PostGIS, but I've got a lot of data that depends on POINT.

> So, if you don't like
> Bruno's subselect-based workaround, the dummy aggregate seems the way
> to go.

I've actually implemented the dummy aggregate now, and it works fine. So
does Bruno's subselect (thank you Bruno), and the efficiency seems to be
similar in each case.

> SQL99 contains a whole bunch of verbiage whose intent seems to be that
> if you GROUP BY a unique or primary-key column, you can reference the
> other columns of that table without aggregation (essentially, the
> system treats them as implicitly GROUP BY'd). Sooner or later we'll
> probably get around to implementing that, and that would solve your
> problem as long as you declare location.ident properly.

That makes a lot of sense, though I imagine there are higher priorities.

Thanks for your help.

Julian Scarfe

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Devrim GUNDUZ 2002-08-19 11:22:08 performance comparison: DISTINCT and GROUP BY
Previous Message Sugandha Shah 2002-08-19 07:29:47 Re: Few Queries