Thread: Random Weighted Result Ordering

Eliot Gable <egable+pgsql-general@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
foreach 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
distinctpriority, 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
thatdoes 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
datathat 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
whichholds 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 wrote:
> rows. Basically, I thought that if the original data was:
>
> 50, 1, 5, 'data1'
> 55, 1, 4, 'data2'
> 34, 2, 0, 'data3'
> 90, 2, 1, 'data4'
> 95, 2, 1, 'data5
>
> And the input to the functions was:
>
> 50, 1, 5
> 55, 1, 4
> 34, 2, 0
> 90, 2, 1
> 95, 2, 1
>
> And the prioritized and weighted order came back:
>
> 50, 1, 5
> 55, 1, 4
> 95, 2, 1
> 90, 2, 1
> 34, 2, 0
>
> Then, if I INNER JOINED them like:
>
> (
>    50, 1, 5
>    55, 1, 4
>    95, 2, 1
>    90, 2, 1
>    34, 2, 0
> ) AS randomized INNER JOIN (
>    50, 1, 5, 'data1'
>    55, 1, 4, 'data2'
>    34, 2, 0, 'data3'
>    90, 2, 1, 'data4'
>    95, 2, 1, 'data5
> ) AS data ON (
> randomized.id <http://randomized.id> = data.id <http://data.id>
> )
>
> Then the rows would come back as:
>
> 50, 1, 5, 'data1'
> 55, 1, 4, 'data2'
> 95, 2, 1, 'data5'
> 90, 2, 1, 'data4'
> 34, 2, 0, 'data3
>
> Unfortunately, that does not seem to be happening. Before I spend a ton
> of time digging into this issue, I thought I would pose the questions here:
>
> Does anyone know for certain why this would not work? Or, does anyone
> know why this should work?

It should not "work" in the sense you mean, but it does "work" in the way that
SQL promises, namely that the order can be anything if you omit an ORDER BY
clause in the SELECT.

> I assumed that the order of the joins would preserve the ordering of the
> first set of data. However, I am worried about how the planner might
> re-arrange the joins on me, and I am wondering whether the order is
> guaranteed to be preserved like this in the first place... Does anyone
> know for sure about these assumptions?

SELECT makes no promise about the order of returned rows absent an ORDER BY
clause.  Your query lacks an ORDER BY clause.  You could therefore get any
order back, including the possibility of different orders from different runs
of the same query.

Tables in a relational database have no inherent order.  You took no steps
whatsoever to guarantee the order of rows returned by the SELECT, so you
should not be surprised at any order that comes back.

--
Lew