sort return data with SQL in SQLite order by row

Question

I have a DB which has 8 columns, all are integers range 1~99:

Col1    Col2    Col3    Col4    Col5    Col6    Col7    Col8
1       13      24      18      35      7       50      88
13      4       33      90      78      42      26      57
22      18      30      3       57      90      71      8

...

When I perform "select Col1, Col2, Col3, Col5, Col6, Col7, Col8 from MyTable where Col4>10" I would like the return data is sorted, e.g. the first row should return like this:

1,7,13,24,35,50,88

However, "order by" only work on "Column", is there anyway to preform this in SQL ? Or need a temp table/max() to perform this ? Thanks.

Regds

LAM Chi-fung

Answer 1

Your current design is not appropriate for this requirement.
Consider changing it to something like this:

CREATE TABLE tablename (
  id INTEGER, -- corresponds to the rowid of your current table 
  col_id INTEGER NOT NULL, -- 1-8, corresponds to the number of each of the columns ColX 
  value INTEGER NOT NULL  -- corresponds to the value of each of the columns ColX  
);

You can populate it from your current table:

INSERT INTO tablename (id, col_id, value)
SELECT rowid, 1, Col1 FROM MyTable UNION ALL
SELECT rowid, 2, Col2 FROM MyTable UNION ALL
SELECT rowid, 3, Col3 FROM MyTable UNION ALL
SELECT rowid, 4, Col4 FROM MyTable UNION ALL
SELECT rowid, 5, Col5 FROM MyTable UNION ALL
SELECT rowid, 6, Col6 FROM MyTable UNION ALL
SELECT rowid, 7, Col7 FROM MyTable UNION ALL
SELECT rowid, 8, Col8 FROM MyTable

Now you can get the result that you want with GROUP_CONCAT() window function and aggregation:

SELECT result
FROM (
  SELECT id, GROUP_CONCAT(value) OVER (PARTITION BY id ORDER BY value) result
  FROM tablename
  WHERE id IN (SELECT id FROM tablename WHERE col_id = 4 AND value > 10)
)
GROUP BY id
HAVING MAX(LENGTH(result))

See the demo.
Results:

result
1,7,13,18,24,35,50,88
4,13,26,33,42,57,78,90