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
Sorting ROWS in stead of COLUMNS ASC or DESC
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Sorting ROWS in stead of COLUMNS ASC or DESC
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Sorting ROWS in stead of COLUMNS ASC or DESC
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 ) :
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
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 ) :
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'
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'
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'
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'
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Thu Jul 14, 2022 12:50 am, edited 1 time in total.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sorting ROWS in stead of COLUMNS ASC or DESC
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
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
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Sorting ROWS in stead of COLUMNS ASC or DESC
Check the Calc help file and/or forum for TRANSPOSEdreamquartz 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
If that doesn't get what you need, post a new topic in the Calc forum
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sorting ROWS in stead of COLUMNS ASC or DESC
@RusselB,
I am indeed looking into the usage, but it appears quite a challenge for what I need to accomplish.
Dream
I am indeed looking into the usage, but it appears quite a challenge for what I need to accomplish.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Sorting ROWS in stead of COLUMNS ASC or DESC
@Sliderule,
I will certainly look into it.
Let you know if it work for me.
Dream
I will certainly look into it.
Let you know if it work for me.
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.