Thread: Custom sorting
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
Jana <jana.vasseru@gmail.com> writes:
> 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" -
Seems like what you need to do is set up this column as a custom
datatype with its own comparison operators. If you make a btree
index opclass for the datatype then ORDER BY will understand that
that's how it should sort the values.
regards, tom lane
On 2009-05-16, Jana <jana.vasseru@gmail.com> wrote:
> 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}
> 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
to me this does not look like it defines an ordering
in other words a>b and b>c does not imply a>c
a {1,111,33,2,9)
b {1,222,44,3,8}
c {1,333,55,4,7}
F(a,b)= 9-8 = 1 thus a>b
F(b,c)= 55-44 = 11 thus b>c
F(a,c)= 2-3 = -1 thus c>a
What you ask is therfore impossible.
if you can create an ordering that is consistent where a>b and b>c
does imply a>c you can then create an operator class for it
(I have no idea how to do this bit)
And then you can create a btree index using it.
On Tue, 19 May 2009 14:00:49 +0200, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2009-05-16, Jana <jana.vasseru@gmail.com> wrote:
>> 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}
>
>> 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
>
> to me this does not look like it defines an ordering
>
> in other words a>b and b>c does not imply a>c
> a {1,111,33,2,9)
> b {1,222,44,3,8}
> c {1,333,55,4,7}
>
> F(a,b)= 9-8 = 1 thus a>b
> F(b,c)= 55-44 = 11 thus b>c
> F(a,c)= 2-3 = -1 thus c>a
>
> What you ask is therfore impossible.
>
>
> if you can create an ordering that is consistent where a>b and b>c
> does imply a>c you can then create an operator class for it
>
> (I have no idea how to do this bit)
>
> And then you can create a btree index using it.
>
>
Thank you (and Tom Lane) for advices. Your point about the sort order is
very valid, the data howerver should not create such situtation (however
this is not 100%). I was trying to get into the custom data types and that
stuff, and then i relazid that it is still no good, because it is very
hard for the user to interpret theese values - i mean - if i (as a user)
see that
{1,111,33,2,9) > {1,222,44,3,8} i still have no idea why.
Well then i came with an idea that i could define another column, which is
computed as: a number of values which (when compared to this value) are
worse than this value. Which pretty much solves all the problems ;)
Regards,
Jana