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 - ]