Thread: Sort question - Fractions, Metric etc

Sort question - Fractions, Metric etc

From
Bret Stern
Date:
I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 
1., 1.125)

Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column 
and sorted based

on those values eg; 1,2,3,4,5,6 indicating the value I need to show in 
which sequence.


Just curious what the pro's do

Bret




Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 9:14 AM, Bret Stern wrote:
> I have a table with metric, imperial, fraction columns.
> 
> Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 
> 1., 1.125)

Alright how is this different from metric or fraction?

I can sort of see fraction if you mean as 1/3, 1/20, etc.

What is the data type of the field you are storing this in?

> 
> Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column 
> and sorted based
> 
> on those values eg; 1,2,3,4,5,6 indicating the value I need to show in 
> which sequence.
> 
> 
> Just curious what the pro's do
> 
> Bret
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Bret Stern
Date:
Strings;

I haven't explored doing this with numeric types, but some columns 
needed alpha chars eg 13mm.

Although I could have front ended this UI with mm nomenclature I did not.

I'll put a table together with appropriate numeric types and see if the 
sort will behave.



On 8/14/2021 9:21 AM, Adrian Klaver wrote:
> On 8/14/21 9:14 AM, Bret Stern wrote:
>> I have a table with metric, imperial, fraction columns.
>>
>> Is there a way to sort correctly using imperial (eg; .125, .375, .437 
>> -> 1., 1.125)
>
> Alright how is this different from metric or fraction?
>
> I can sort of see fraction if you mean as 1/3, 1/20, etc.
>
> What is the data type of the field you are storing this in?
>
>>
>> Couldn't handle it with ORDER BY ASC, DESC args so I added a 
>> sort_column and sorted based
>>
>> on those values eg; 1,2,3,4,5,6 indicating the value I need to show 
>> in which sequence.
>>
>>
>> Just curious what the pro's do
>>
>> Bret
>>
>>
>>
>
>



Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 9:37 AM, Bret Stern wrote:
> Strings;
> 
> I haven't explored doing this with numeric types, but some columns 
> needed alpha chars eg 13mm.

Two columns:

data_val(numeric)    data_unit(varchar)
13            mm

> 
> Although I could have front ended this UI with mm nomenclature I did not.
> 
> I'll put a table together with appropriate numeric types and see if the 
> sort will behave.

It will.

> 
> 
> 
> On 8/14/2021 9:21 AM, Adrian Klaver wrote:
>> On 8/14/21 9:14 AM, Bret Stern wrote:
>>> I have a table with metric, imperial, fraction columns.
>>>
>>> Is there a way to sort correctly using imperial (eg; .125, .375, .437 
>>> -> 1., 1.125)
>>
>> Alright how is this different from metric or fraction?
>>
>> I can sort of see fraction if you mean as 1/3, 1/20, etc.
>>
>> What is the data type of the field you are storing this in?
>>
>>>
>>> Couldn't handle it with ORDER BY ASC, DESC args so I added a 
>>> sort_column and sorted based
>>>
>>> on those values eg; 1,2,3,4,5,6 indicating the value I need to show 
>>> in which sequence.
>>>
>>>
>>> Just curious what the pro's do
>>>
>>> Bret
>>>
>>>
>>>
>>
>>


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Bret Stern
Date:
I like that idea

On 8/14/2021 9:46 AM, Adrian Klaver wrote:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> Strings;
>>
>> I haven't explored doing this with numeric types, but some columns 
>> needed alpha chars eg 13mm.
>
> Two columns:
>
> data_val(numeric)    data_unit(varchar)
> 13            mm
>
>>
>> Although I could have front ended this UI with mm nomenclature I did 
>> not.
>>
>> I'll put a table together with appropriate numeric types and see if 
>> the sort will behave.
>
> It will.
>
>>
>>
>>
>> On 8/14/2021 9:21 AM, Adrian Klaver wrote:
>>> On 8/14/21 9:14 AM, Bret Stern wrote:
>>>> I have a table with metric, imperial, fraction columns.
>>>>
>>>> Is there a way to sort correctly using imperial (eg; .125, .375, 
>>>> .437 -> 1., 1.125)
>>>
>>> Alright how is this different from metric or fraction?
>>>
>>> I can sort of see fraction if you mean as 1/3, 1/20, etc.
>>>
>>> What is the data type of the field you are storing this in?
>>>
>>>>
>>>> Couldn't handle it with ORDER BY ASC, DESC args so I added a 
>>>> sort_column and sorted based
>>>>
>>>> on those values eg; 1,2,3,4,5,6 indicating the value I need to show 
>>>> in which sequence.
>>>>
>>>>
>>>> Just curious what the pro's do
>>>>
>>>> Bret
>>>>
>>>>
>>>>
>>>
>>>
>
>



Re: Sort question - Fractions, Metric etc

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns 
>> needed alpha chars eg 13mm.

> Two columns:

> data_val(numeric)    data_unit(varchar)
> 13            mm

It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.

            regards, tom lane

[1] https://github.com/df7cb/postgresql-unit



Re: Sort question - Fractions, Metric etc

From
Sándor Daku
Date:


On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns
>> needed alpha chars eg 13mm.

> Two columns:

> data_val(numeric)     data_unit(varchar)
> 13                    mm

It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.

                        regards, tom lane

[1] https://github.com/df7cb/postgresql-unit


I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess.  

Regards,
Sándor

Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 10:19 AM, Sándor Daku wrote:
> 

> I'm not sure, but maybe a kind of cast function that cast everything 
> into a reasonable common unit(Khm... millimeters) and sort on that? It 
> seems relatively simple to pick up the value and unit from a string with 
> a regexp. Admittedly the data would still be a mess.

I see the potential for another crash landing:) :

https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_failure


> 
> Regards,
> Sándor


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
"bret_stern@machinemanagement.com"
Date:
Of course it's a complete disorganized mess..
That'a the world I live in.

Best thing is, I'm walking away with some new strategies.

You guys rock

-------- Original message --------
From: Sándor Daku <daku.sandor@gmail.com>
Date: 8/14/21 10:19 AM (GMT-08:00)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Bret Stern <bret_stern@machinemanagement.com>, pgsql-general@lists.postgresql.org
Subject: Re: Sort question - Fractions, Metric etc



On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns
>> needed alpha chars eg 13mm.

> Two columns:

> data_val(numeric)     data_unit(varchar)
> 13                    mm

It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.

                        regards, tom lane

[1] https://github.com/df7cb/postgresql-unit


I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess.  

Regards,
Sándor

Re: Sort question - Fractions, Metric etc

From
"Gogala, Mladen"
Date:
I would write a stable function converting everything to metric (or 
imperial, depends on your preferences) and sort on the return of the 
function. Since unit conversion functions do not need to modify the 
database and should always return the same values for the same 
arguments, the function can be used within a query (that is the meaning 
of the keyword "STABLE")  and you can use it for sorting stuff

Regards

On 8/14/2021 12:14 PM, Bret Stern wrote:
> I have a table with metric, imperial, fraction columns.
>
> Is there a way to sort correctly using imperial (eg; .125, .375, .437 
> -> 1., 1.125)
>
> Couldn't handle it with ORDER BY ASC, DESC args so I added a 
> sort_column and sorted based
>
> on those values eg; 1,2,3,4,5,6 indicating the value I need to show in 
> which sequence.
>
>
> Just curious what the pro's do
>
> Bret
>
>
-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Blog: https://dbwhisperer.wordpress.com



Re: Sort question - Fractions, Metric etc

From
Guyren Howe
Date:
You might define new types for temperature, length, whatever, with suitable conversion, operation and creation functions. You’d be able to define how the new types participate in indexes, support directly sorting on them, so you package up this complexity and forget about it.

Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display).

Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified.

Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit
On Aug 14, 2021, 12:51 -0700, Gogala, Mladen <gogala.mladen@gmail.com>, wrote:
I would write a stable function converting everything to metric (or
imperial, depends on your preferences) and sort on the return of the
function. Since unit conversion functions do not need to modify the
database and should always return the same values for the same
arguments, the function can be used within a query (that is the meaning
of the keyword "STABLE")  and you can use it for sorting stuff

Regards

On 8/14/2021 12:14 PM, Bret Stern wrote:
I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, .437
-> 1., 1.125)

Couldn't handle it with ORDER BY ASC, DESC args so I added a
sort_column and sorted based

on those values eg; 1,2,3,4,5,6 indicating the value I need to show in
which sequence.


Just curious what the pro's do

Bret


--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Blog: https://dbwhisperer.wordpress.com


Re: Sort question - Fractions, Metric etc

From
Bret Stern
Date:

Here's the clip of the UI. The user selects whatever value the vendor provides for unit thickness. The data entry

people aren't comfortable converting.

At this point the sort_order column managed to do the trick....but going forward on my next

application, I can see a different approach.

Are you suggesting a function like this shellsort used with an array.

https://www.geeksforgeeks.org/shellsort/

// C++ implementation of Shell Sort

#include <iostream>
using namespace std;

/* function to sort arr using shellSort */
int shellSort(int arr[], int n)
{
    // Start with a big gap, then reduce the gap
    for (int gap = n/2; gap > 0; gap /= 2)
    {
        // Do a gapped insertion sort for this gap size.
        // The first gap elements a[0..gap-1] are already in gapped order
        // keep adding one more element until the entire array is
        // gap sorted
        for (int i = gap; i < n; i += 1)
        {
            // add a[i] to the elements that have been gap sorted
            // save a[i] in temp and make a hole at position i
            int temp = arr[i];

            // shift earlier gap-sorted elements up until the correct
            // location for a[i] is found
            int j;       
            for (j = i; j >= gap && arr[j - gap] > temp; j -= gap)
                arr[j] = arr[j - gap];
           
            // put temp (the original a[i]) in its correct location
            arr[j] = temp;
        }
    }
    return 0;
}

void printArray(int arr[], int n)
{
    for (int i=0; i<n; i++)
        cout << arr[i] << " ";
}

int main()
{
    int arr[] = {12, 34, 54, 2, 3}, i;
    int n = sizeof(arr)/sizeof(arr[0]);

    cout << "Array before sorting: \n";
    printArray(arr, n);

    shellSort(arr, n);

    cout << "\nArray after sorting: \n";
    printArray(arr, n);

    return 0;
}

Array before sorting:
12 34 54 2 3
Array after sorting:
2 3 12 34 54




On 8/14/2021 12:51 PM, Gogala, Mladen wrote:
I would write a stable function converting everything to metric (or imperial, depends on your preferences) and sort on the return of the function. Since unit conversion functions do not need to modify the database and should always return the same values for the same arguments, the function can be used within a query (that is the meaning of the keyword "STABLE")  and you can use it for sorting stuff

Regards

On 8/14/2021 12:14 PM, Bret Stern wrote:
I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125)

Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based

on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence.


Just curious what the pro's do

Bret


Attachment

Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 1:24 PM, Bret Stern wrote:
> Here's the clip of the UI. The user selects whatever value the vendor 
> provides for unit thickness. The data entry

So the vendors supply the measurements in all the various units for a 
given item?

> 
> people aren't comfortable converting.
> 
> At this point the sort_order column managed to do the trick....but going 
> forward on my next
> 
> application, I can see a different approach.
> 
> Are you suggesting a function like this shellsort used with an array.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Bret Stern
Date:
Yep,

I provide the UI for the user to select the vendor supplied unit value 
from the list.

I don't want anyone entering (manually) any value, they must select from 
my list (fed from a PG table).

(and since some units don't translate exactly, they pick the one that is 
closest).

It's Tile (floor, shower etc), not the tightest tolerance of dimensions, 
so it's not catastrophic if it's off

a 32nd or so.

cheers





On 8/14/2021 1:43 PM, Adrian Klaver wrote:
> On 8/14/21 1:24 PM, Bret Stern wrote:
>> Here's the clip of the UI. The user selects whatever value the vendor 
>> provides for unit thickness. The data entry
>
> So the vendors supply the measurements in all the various units for a 
> given item?
>
>>
>> people aren't comfortable converting.
>>
>> At this point the sort_order column managed to do the trick....but 
>> going forward on my next
>>
>> application, I can see a different approach.
>>
>> Are you suggesting a function like this shellsort used with an array.
>
>



Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 2:04 PM, Bret Stern wrote:
> Yep,
> 
> I provide the UI for the user to select the vendor supplied unit value 
> from the list.

I should have been more specific, does the vendor do all the conversions 
and supply them to you?

> 
> I don't want anyone entering (manually) any value, they must select from 
> my list (fed from a PG table).
> 
> (and since some units don't translate exactly, they pick the one that is 
> closest).

Yes, nominal dimensions. That is what I'm trying to work out, is this 
something that makes sense to the vendor and should not be tampered with?

> 
> It's Tile (floor, shower etc), not the tightest tolerance of dimensions, 
> so it's not catastrophic if it's off

Is it only tile you are working with?

> 
> a 32nd or so.
> 
> cheers
> 
> 
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Bret Stern
Date:
On 8/14/2021 2:13 PM, Adrian Klaver wrote:
> On 8/14/21 2:04 PM, Bret Stern wrote:
>> Yep,
>>
>> I provide the UI for the user to select the vendor supplied unit 
>> value from the list.
>
> I should have been more specific, does the vendor do all the 
> conversions and supply them to you?
>
Vendors supply dimensions, depending on where in the world the product 
comes from, could be metric, imperial

or fraction


>>
>> I don't want anyone entering (manually) any value, they must select 
>> from my list (fed from a PG table).
>>
>> (and since some units don't translate exactly, they pick the one that 
>> is closest).
>
> Yes, nominal dimensions. That is what I'm trying to work out, is this 
> something that makes sense to the vendor and should not be tampered with?
>
The vendor is out of the picture at this point. They provide a catalog, 
and we enter (or import) items into our POS system. We match their 
dimension within tolerance.
>>
>> It's Tile (floor, shower etc), not the tightest tolerance of 
>> dimensions, so it's not catastrophic if it's off
>
> Is it only tile you are working with?

No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks 
and delivered in blocks of ten slabs normally, where tolerances are 
also  +/- .032

I will say this business has been behind in the attributes game. Plus 
there are many "artsy" vendors

who can hardly speak in these terms, and don't publish to us, so we do 
the best we can.

Getting vendors to supply the basic values is a struggle.

>
>>
>> a 32nd or so.
>>
>> cheers
>>
>>
>>
>>
>
>
>



Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 2:47 PM, Bret Stern wrote:
> 
> On 8/14/2021 2:13 PM, Adrian Klaver wrote:
>> On 8/14/21 2:04 PM, Bret Stern wrote:


> Vendors supply dimensions, depending on where in the world the product 
> comes from, could be metric, imperial
> 
> or fraction
> 
>> Yes, nominal dimensions. That is what I'm trying to work out, is this 
>> something that makes sense to the vendor and should not be tampered with?
>>
> The vendor is out of the picture at this point. They provide a catalog, 
> and we enter (or import) items into our POS system. We match their 
> dimension within tolerance.
>

It would seem then a entry system that specifies the initial input 
unit(metric, imperial) and then does the conversion to a set unit(my 
preference would be metric) for storage to the table in that unit. Then 
you would convert on the fly to fill out the display field. Or you could 
use something like generated 
columns(https://www.postgresql.org/docs/12/ddl-generated-columns.html) 
available in Postgres 12+ to pre-fill  columns to save the overhead on 
subsequent queries. For pre-12 maybe a trigger on the table column to 
fill in the other columns. In any case you sort(order by) by your 
canonical column which would be of numeric type.


> No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks 
> and delivered in blocks of ten slabs normally, where tolerances are 
> also  +/- .032
> 
> I will say this business has been behind in the attributes game. Plus 
> there are many "artsy" vendors
> 
> who can hardly speak in these terms, and don't publish to us, so we do 
> the best we can.
> 
> Getting vendors to supply the basic values is a struggle.
> 
>>
>>>
>>> a 32nd or so.
>>>
>>> cheers
>>>
>>>
>>>
>>>
>>
>>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Gavan Schneider
Date:
On 15 Aug 2021, at 7:47, Bret Stern wrote:

> I will say this business has been behind in the attributes game. Plus  there are many "artsy" vendors who can hardly
speakin these terms, and don't publish to us, so we do the best we can. 
>
>  Getting vendors to supply the basic values is a struggle.
>
I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how
much(mostly in units of dollar) 😉 
After that there may be length width and thickness values specified with (hopefully) the same units ± weight (per unit
orbox?) ± allowance  for grout/joining (in case your application is going to be used as an aid in estimating quantities
needed)
The truly artistic supplier will refrain from making anything the same and your customer will be expected to buy the
itemfirst and plan their project around it. 

One possible data wrangling scheme would be to give each item a catalogue number (yours) — Just noticed Adrain saying
somethingsimilar so apologies for the overlap. 
The vendor_spec table would capture the vendor’s identifier, description and supplied dimensions (with a units column).
The dimensions_view (suggest a materialised view) would carry the dimension information in converted form, e.g., mm:
numeric(8,3)
Once the conversions are setup the customer can be supplied with dimensions in the system of their choice and you have
asensible common point reference for any rankings. 

When I first saw this thread I thought you were getting into metal dimensions and started to wonder if your next phase
wasto “organise” bolts according to diameter length and thread… you have picked the easier course, the other is a
nightmare,e.g., https://www.americanmachinetools.com/machinist_tables.htm 

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong. 
— H. L. Mencken, 1920



Re: Sort question - Fractions, Metric etc

From
Adrian Klaver
Date:
On 8/14/21 4:05 PM, Gavan Schneider wrote:
> On 15 Aug 2021, at 7:47, Bret Stern wrote:
> 
>> I will say this business has been behind in the attributes game. Plus  there are many "artsy" vendors who can hardly
speakin these terms, and don't publish to us, so we do the best we can.
 
>>
>>   Getting vendors to supply the basic values is a struggle.
>>
> I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and
howmuch (mostly in units of dollar) 😉
 

Hmm, you are lucky:)

Of late I get 'who knows' for how many and 'we'll see' for how much.

> Gavan Schneider
> ——
> Gavan Schneider, Sodwalls, NSW, Australia
> Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong.
 
> — H. L. Mencken, 1920
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Sort question - Fractions, Metric etc

From
Pól Ua Laoínecháin
Date:
> I have a table with metric, imperial, fraction columns.

Have  "metric_equivalent_magnitude" and "metric_equivalent_unit"
columns and do all your sorting &c. via those columns. Use the
relatively new generated (or computed/calculated) columns to calculate
these. That way, you get the conversion correct once and you don't
have to worry about it any more!

That way, you can compare between cubits, furlongs and kilometres or
barrels, hogsheads and litres & American and British imperial units.

You might also want a metric_equivalent_type to ensure that you're not
comparing lengths with volumes, masses or velocities... &c...?

Pól...