RE: [GENERAL] How to do this in Postgres

Hello,

On Tue, 23 Nov 1999 Fabian.Frederick@prov-liege.be wrote:

> > Hi there,
> >
> > I tried all I could think of with the following problem, perhaps
> > someone has another idea.
> >
> > I have a table where for each id there may (and often are) multiple
> > rows with some kind of priority.
> >   create table data ( id1 int4, id2 int4, <<lots of data>>,
> > prio int4 );
> > The minimal priority is not guaranteed to be 1. There are 200k
> > different ids with up to 10 entries, summing up to 400k rows.
> >
> > Not I want to do something like this:
> >
> >     select * from data where <<prio is minimal per id pair>>.
> >

I'm going to stop here and try and give you a query that does exactly
that (I think).

How about:

select * from data
where (prio,id1,id2) = (
SELECT min(prio) from data d2 where d2.id1=1 and d2.id2=2 group by
d2.id1,d2.id2
);

Seems to work on my -very limited- test table.  Trickiest bit was to avoid
the "ERROR:  parser: Subselect has too many or too few fields." message
you get if you specify 'where id1=1 and id2=2' in the main SELECT and just
and 'AND prio=(SELECT min(prio)...' [do ignore this if it makes no
sense!].

Should be easy to turn that into a SELECT GetData( int4, int4 ) like
function if you want.

HTH,

Stuart.

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk