Apache OpenOffice Community Forum

Hello All,

I am trying to sort ROWS ASC or DESC

Principle
Row length x items
Sort a selection of items, selected from row

Example
Row: items numbered 1-40 (therefore 40 columns), but number of items is random
Selection 7 random numbers, but number of selection could be random:
a. 3, 12, 8, 23, 17, 33, 10
b. 4, 27, 39, 3, 11, 20, 8
c. 1, 2, 3, 14, 30, 5, 11

ORDER resultset required:
a1: 3, 8, 10, 12, 17, 23, 33
b1: 3, 4, 8, 11, 20, 27, 39
c1: 1, 2, 3, 5, 11, 14, 30

There are multiple rows, in this example all random selection of 7 numbers, and all need to be ordered by row.
The number of items can be set by the User, and will not change for the Query
The number of selected items can be set by the User, and will not change for the Query

For a column it is simple, but for a row, I can not seem to make it happen.

Any suggestions?

Thanks in advance,

Dream

Since HSQL Version 2.1.0 circa 2010, the Aggregate Function GROUP_CONCAT is available for use ( NOT available with Embedded HSQL 1.8.0.10 ).

You might also need the use of UNION ALL assuming you want to return more than one row of data.

Since the OpenOffice / LibreOffice Base Parser does NOT accept an Order By NOR a Separator clause in the GROUP_CONCAT syntax, this Query would have to be saved or run using Direct rather then with Base Parser.

Read HSQL documentation, or, any SQL tutorial on the subject ( GROUP_CONCAT ).

Examples of SQL that can be used, even on your computer as is, making sure to run withOUT Base Parser (
Run Direct ) :

1. Code: Select all

   Select 
      INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME  as "TABLE_NAME",
      INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY as NUM_RECORDS
   From INFORMATION_SCHEMA.SYSTEM_TABLESTATS
   Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = 'PUBLIC'

2. Code: Select all

   Select 
      GROUP_CONCAT(INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY) as NUM_RECORDS
   From INFORMATION_SCHEMA.SYSTEM_TABLESTATS
   Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = 'PUBLIC'
   

3. Code: Select all

   Select 
      GROUP_CONCAT(INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY Order By INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY asc Separator ', ') as NUM_RECORDS
   From INFORMATION_SCHEMA.SYSTEM_TABLESTATS
   Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = 'PUBLIC'
   

4. Code: Select all

   Select 
      GROUP_CONCAT(INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME || ': ' || INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY Order By INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY asc Separator ', ') as NUM_RECORDS
   From INFORMATION_SCHEMA.SYSTEM_TABLESTATS
   Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = 'PUBLIC'
   

I hope this helps, please be sure to let me / us know.

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Thanks for the suggestion.
Will look into it.
Am also looking into transposing a Array via Calc, and use it as a source for the 'normal' ORDER BY.

Dream

dreamquartz wrote: ↑Thu Jul 14, 2022 12:38 am Thanks for the suggestion.
Will look into it.
Am also looking into transposing a Array via Calc, and use it as a source for the 'normal' ORDER BY.

Dream

Check the Calc help file and/or forum for TRANSPOSE
If that doesn't get what you need, post a new topic in the Calc forum

@RusselB,

I am indeed looking into the usage, but it appears quite a challenge for what I need to accomplish.

Dream

@Sliderule,

I will certainly look into it.
Let you know if it work for me.

Dream