Great suggestion. Thanks. Don't know why I didn't think of that. I do almost exactly the same thing further down in my stored procedure.
On Mon, Jun 7, 2010 at 4:34 AM, Dimitri Fontaine <dfonta...@hi-media.com>wrote: > Eliot Gable <egable+pgsql-gene...@gmail.com<egable%2bpgsql-gene...@gmail.com>> > writes: > > > I have a set of results that I am selecting from a set of tables which I > want to return in a random weighted order for each priority group returned. > Each row has a > > priority column and a weight column. I sort by the priority column with 1 > being highest priority. Then, for each distinct priority, I want to do a > weighted random > > ordering of all rows that have that same priority. I select the set of > rows and pass it to a custom-built function that does the ordering. I have > tested both the > > prioritize and the random weighted ordering functions and they do exactly > what I want them to do for ordering the data that I send them. > > > > The problem comes from the fact that I tried to make them generalized. > They take an array of a small complex type which holds just an arbitrary ID, > the priority, > > and the weight. The output is the same information but the rows are in > > the correct order. > > I'd try having the function return just numbers in the right order, then > use that in the ORDER BY. To have those numbers, you'd still need to > join with the result of the function, tho. > > Hope this helps you already, I don't have time to go deeper in the > subject! > > Regards, > -- > dim > -- Eliot Gable "We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower "I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower "Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
