Hi,
i'm looking for a way to create a custom sort table. The table has a
column which is currently character varying (255), although i might do
with an array of double if it will help. This column can contain various
data as it represents an output of a polymorfic algorithm, this is usualy
a sequence of numbers which have to be compared with some pretty complex
rules.
Example values:
{23.4;324;54.3;12.3}
{23.4;53;64.4;53.5}
{23.4;123;54.4;43.5}
{43.2;563}
{54.1;342}
{23.4;433;33.5}
{A;B;C;D;E}
Sample comparison pseudocode:
function (R1, R2)
if (R1.A == R2.A)
if (R1.B > 200) and (R2.B > 200)
if (R1.B > 1000) and (R2.B > 1000)
return R1.D - R2.D;
else
return R2.C - R1.C;
else
if (R1.C < 50) and (R2.C < 50)
return R1.E - R2.E;
else
return R1.D - R2.D;
else
return R1.A - R2.A
Values above sorted in descending order:
{54.1;342}
{43.2;563}
{23.4;433;33.5;12}
{23.4;324;54.3;12.3;45}
{23.4;123;54.4;43.5;43}
{23.4;53;64.4;53.5;23}
{23.4;433;33.5;12.2}
I would like to make this ordering on database side since there are two
appliactions which use the data and both would have to reimplement the
ordering mechanism. I read several articles on custom sorting, which show
that i can use any function in the ORDER BY clause, however all the
examples assume only one input value from which the custom function
genereates some sort of automatically comparable value. However this
always involves only a single field - which i cannot use since i need two
rows to say which one is "higher" and "lower" - as the example tries to
show the absolute value returned by such comparison function may vary a
lot and thus is unusuable in ordering, but it can be used to determine
which one of two rows should be higher. How is it possible (if it is
possible ;) to do it? Thanks for help.
Regards,
Jana