Thread: RE: [GENERAL] How to do this in Postgres

> 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>>.
>
> First attempt (deleting non minimal)
> ------------------------------------
>
>     select a.id1, a.id2, a.prio
>     into bugos
>     from a data, b data
>     where a.prio > b.prio and a.id1 = b.id1 and a.id2 = b.id2;
>
>     delete from data
>     where id1 = bogus.id1 and id2 = bogus.id2 and prio = bogus.prio;
>
> The join does not seem to complete. I am not sure whether I should
> have waited longer, but after 4h without significant disk access I
> do not think that this thing will ever return. Indexing didn't help.
>
> Second attempt (stored procedures)
> ----------------------------------
>
>     create function GetData( int4, int4 )
>     returns data
>     as 'select *
>         from data
>         where id1 = $1 and id2 = $2
>         order by prio
>         limit 1'
>     language 'sql';
>
>     select GetData(id1,id2) from <<table with unique ids>>;
>
> limit in functions is not yet implemented in postgres (6.5.2)
>
> Third attempt (use perl on dumped table)
> ----------------------------------------
>
> I don't want to :-)

IMHO, Perl is the solution,
some foreach architecture on @table should work...
Of course it could be ordinal (usable on fixed row system).
The best would be to recover row name as well(if someone has an
idea to do this ???):

    foreach $item in fetched_row{
        #Recover row name (Process runned once (rec#1)
        ???
        #Recover row content
    }


Regards, Fabian

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