Thread: How to handle a group query

Apologies for a longish message, but I'm no SQL expert and am trying to
get my head round some concepts.

What I am trying to do is get a set of rows out of a table based on a
set of grouping constraints.  A simplified form of the table I am using
is:-
        CREATE TABLE grouptest (
         rowid serial PRIMARY KEY,
         grouping integer,
         tstval integer,
         other character varying);

I want to get out rows of data where tstval is at a minimum value for
each set GROUPed by grouping.   If there is more than one row with the
same minimal value for tstval then I want any of those rows (but the
data for a row must be consistent).

I got a way of doing this, but it has *three* nested selects - so it
feels that I am going to be really hammering the database as the table
gets big (need to start considering carefully where indexes should be on
this).

The query I came up with is (wait for it):-
        SELECT * FROM grouptest JOIN
         (SELECT MIN(rowid) AS rowid FROM grouptest AS second JOIN
          (SELECT grouping,MIN(tstval) AS minval FROM grouptest
          GROUP BY grouping)
         AS first
         ON (first.grouping = second.grouping
         AND second.tstval = first.minval)
         GROUP BY first.grouping)
        AS third USING (rowid);

It seems that one alternative way of doing this would be to use a user
defined aggregate function, such that
    MYMIN(rowid,tstval)
returns (one of) the rowid for which tstval was smallest.

This would remove the requirement for one of the nested SELECTs - and
make it much more readable.

Does this seem reasonable, and has anyone a simple example of an
aggregate function like this which I could build on.

Cheers
    Nigel.
--
[ Nigel Metheringham           Nigel.Metheringham@InTechnology.co.uk ]
[ - Comments in this message are my own and not ITO opinion/policy - ]

Nigel Metheringham <Nigel.Metheringham@dev.intechnology.co.uk> writes:
> What I am trying to do is get a set of rows out of a table based on a
> set of grouping constraints.  A simplified form of the table I am using
> is:-
>         CREATE TABLE grouptest (
>          rowid serial PRIMARY KEY,
>          grouping integer,
>          tstval integer,
>          other character varying);

> I want to get out rows of data where tstval is at a minimum value for
> each set GROUPed by grouping.   If there is more than one row with the
> same minimal value for tstval then I want any of those rows (but the
> data for a row must be consistent).

If you don't mind using a Postgres-specific construct, SELECT DISTINCT ON
works nicely for this.  See the "weather reports" example in the SELECT
reference page.

            regards, tom lane

On Thu, 2004-11-04 at 14:15 -0500, Tom Lane wrote:
> If you don't mind using a Postgres-specific construct, SELECT DISTINCT ON
> works nicely for this.  See the "weather reports" example in the SELECT
> reference page.

It took a few minutes for what you were suggesting to click, but now I
am completely blown away by that - its *so* much simpler.

Cheers
    Nigel.
--
[ Nigel Metheringham           Nigel.Metheringham@InTechnology.co.uk ]
[ - Comments in this message are my own and not ITO opinion/policy - ]