Sort order problem

[Alanb2]

When I’m creating reports, I notice that in a field that contains names for instance, a space is treated as a higher value than a character. For example when printing I would expect ‘Al Stewart’ to appear before ‘Alan Stewart’, (as in stardard ascii notation), but in my reports (and querys) these values are reversed. I can’t find any system option that will help.

I think the reply that Michel has given clarifies what I am expecting to happen. I’m simply printing fields from a database table. let me give two examples. Image1.JPG. This shows a few records in ascending order from a spreadsheet, which is the one I consider correct. The second shows how they have been imported into a table (and thus show in a report. Image2.JPG.

To clarify further - Thie first image is captured from a ‘calc’ spreadsheet stored as .ods and stored the data in ascii order, taking note of embedded spaces. The second image is from a ‘base’ table contaning the same data imported from calc. The db column containing the data has been defined as varchar and is the designated primary key . The sort order has been changed in the base table (image_2). What is happening IMHO is that the mechanism that stores the key is treating embedded spaces as the end of the key. I’ve added another image to show how I’ve achieved the order I want by creating an additional key, pretty much as suggested. I think there there is a system fault, but I’ll close this as I have a solution of sorts.

[mgl]

Hello @Alanb2

To my knowledge, the ASCI code of a space is 32, and the one for upper case letters series starts with 65 for A and the one for lower case letters starts with 97 for a. then it looks normal to sort space before upper case letters and these latter before the lower case letters.

I do not see any solution to this. Even splitting first name and last name will sort shorter “Al” before “Alan” but it might help preventing having “Al Vera” before “Alan Stewart”

---

To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.

Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

Kind regards, Michel

[mgl]

Image1.jpg looks “normal”

While image2.jpg looks strange: as if the white spaces have been removed from the fields before sorting the fields and then put back again. To get the same, you could use the method temporarily proposed by @igorlius, before he updated it, meaning that you create another field containing the fields to be sorted whithout any white space by SUBSTITUTE(A1;" ";"") if the field to be sorted is in A1. Then you may sort against this new field.

Kind regards, Michel

[Alanb2]

That’s an ingeneous solution, though it would be a case of the tail wagging the dog. That varchar column is declared as the primary key (it’s the only column in the table). Although this suggestion is a useable workaround, it seems to me that there is a Libre office problem with indexing at the root of my issue.

[mgl]

Hello @Alanb2

Having a single column to store possible homonyms might lead to mis correction of a field related to some items in another table. By example, one fiel is Mike Smith and concerns to homonyms. If suddenly you correct and and up the middle name of one of the homonyms, the second one will get the same middle name, which has great chance not to be true.

Then a primary key is of no impact on the sorting you want, which depends on the way the queries ate build. You may sort one way to show some results and sort another way in another case.

Sorting sorts are a matter of people intent.

If you’re satisfied by the answers to your question, don’t forget to tick in the round mark. Thanks in advance.

If you want to get an improvement of LO, visit https://www.libreoffice.org/community/developers/

Kind regards, Michel

[Alanb2]

The earlier suggestion is slightly flawed. The action of effectively removing a space simply replicates how libre office is (erroneously) treating the name field. However by substituting the space by another character less than ascii 32, for instance ‘!’ in a second version of the column, that sort order can be used.

[mgl]

@Alanb2

Reading once more your question and the image examples you provided is not enough to understand your problem. Maybe I did not pay enough attention to your mention of
I can't find any system option that will help.

In fact ordering in Base is made by the SQL clause ORDER BY, after a WHERE and a GROUP BY and before a HAVING. There is an alternate : either ASC or DESC. Not expressing implicitly the option equals ASC. The sorting order you show in Image1.jpg is the one you get with ORDER BY field_name ASC.

Could you check the proper use of ORDER BY in your queries ? If you are still stuck, please provide an database example with no confidential data.

Kind regards, Michel

[igorlius]

Maybe you could replace / add a (non printable) character before the space.
Just an idea. Not sure if there is actually one you can use. Sorry.

[Alanb2]

The ‘substitute’ suggestion mentioned below, with a minor amendment (substitute " " for “!”), works but isn’t a solution, just a work-around. Thanks for all your comments.

[Frants]

Perhaps the best solution is to split. You need to make two fields - FirstName and LastName. Also add a separate key field.