Why are reports organized by first digit instead of whole number?

sschreiber2785

Is there anyway to have reports organized by whole number instead of the first digit? When we run our "Past Due Balances" report and sort by "unit" the report will go - 06,09,104,122,23,32,38...  The report is improper order and should be organized by whole number.  Does anyone know who I can contact to correct this error?  Also I tried to select "How to?" for the category of question and it wouldn't let me. 

themage

Sitelink is using alpha-numeric sorting, since many of the sites use alpha numeric unit designations (A01, C45, etc).

The only way I can think of right now to sort by whole number is to export it to excel and sort it there.

storable_support

@sschreiber2785 - A bit of background on this one, this is not a SiteLink-specific issue but rather how alphanumeric string sorting works. Unit names are not numbers/integers but character strings -- even if all of a sites' unit names represent a number.

Database logic is that it sorts everything by the first digit, then second, etc.  The only way to overcome this is to make all their unit names the same length. For example, 06 should be 006 and 23 should be 023.. Assuming your max unit number length is 3 digits. If you have unit number length of 4 digits, then make them all 4 digits. 

Hope that helps!

benanamen

"Database logic is that it sorts everything by the first digit, then second, etc.  The only way to overcome this is to make all their unit names the same length."

Hi Storable, the request by @sschreiber2785 is SO simple to implement. Here, I will do it for you as a courtesy to all the customers. Your friendly neighborhood Database Admin.

The following query will work for everyone no matter what their numbering format is.

With this mix of formats: C45, A01, 2 , 1, 03, 01, 003

The query result would be: 1, 2, 01, 03, 003, A01, C45

-- Finally by string value<br>SELECT *
FROM units
ORDER BY 
    CASE WHEN id REGEXP '^[0-9]+$' THEN 1 ELSE 2 END, -- Numbers first, then letters
    LENGTH(unit_id),  -- Then by string length
    id; 

* By the way, I am available if you want to hire me.