RE: [GENERAL] How to do this in Postgres
От | Stuart Rison |
---|---|
Тема | RE: [GENERAL] How to do this in Postgres |
Дата | |
Msg-id | Pine.LNX.4.10.9911231550410.15806-100000@bsmlx17 обсуждение исходный текст |
Ответ на | RE: [GENERAL] How to do this in Postgres (Fabian.Frederick@prov-liege.be) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: