Is there any way to order result by column value?

Question

I have MySQL table with many columns.

id|Date      |col1|col2|col3|col4|col5|...                |col500|
-----------------------------------------------------------------
 1|01.10.2019| 152|  99|   0|1598|  48|  filled with zeros|     1|
-----------------------------------------------------------------
 2|02.10.2019|  12| 344|  19|2544|   3|  filled with zeros|   152|
-----------------------------------------------------------------
....

Is it possible to order column

SELECT * FROM table WHERE Date = '02.10.2019' ORDER BY COLUMNS

and get result like this

id|Date       |col4|col2|col500|col3|col1|col5|...              |
-----------------------------------------------------------------
 2|02.10.2019 |2544| 344|   152|  19|  12|   3|filled with zeros

No. The result set columns are fixed. (And decided at compile time, before data is read.) — jarlh
How does it matter ? Ordering within columns does not matter at all, especially when you take the query result out as an associative array. Moreover, the moment you have multiple columns having similar attributes, it reeks of a really bad design. — Madhur Bhaiya

Feacio Feacio · Accepted Answer · 2019-10-03T08:51:20

If you want to order by columns value, as Madhur Bhaiya and P.Salmon said, may be your table is not properly designed, and you are probably speaking of rows, and not of columns. Instead of a table with columns ID/ Date/ col1/ col2/ ... / col500, perhaps you need a table like this

CREATE TABLE tbl(
pk integer not null primary key default autoincrement,
id integer not null,
dt date not null,
xxx_id integer,
value integer);

where XXX_ID represent the column position in your old table (may be an identifier of single record, I don't know what you are using this table for).

Now you have to insert the values:

INSERT INTO tbl (id, date, value) VALUES (1, '2019-10-01', 152);
INSERT INTO tbl (id, date, value) VALUES (1, '2019-10-01', 99);
INSERT INTO tbl (id, date, value) VALUES (1, '2019-10-01', 0);
INSERT INTO tbl (id, date, value) VALUES (1, '2019-10-01', 1592);
INSERT INTO tbl (id, date, value) VALUES (1, '2019-10-01', 48);
....
INSERT INTO tbl (id, date, value) VALUES (2, '2019-10-02', 12);
INSERT INTO tbl (id, date, value) VALUES (2, '2019-10-02', 344);
INSERT INTO tbl (id, date, value) VALUES (2, '2019-10-02', 19);
INSERT INTO tbl (id, date, value) VALUES (2, '2019-10-02', 2544);
...

And the answer to your question becomes very easy, something like

select * from tbl 
where id = 1 AND date = '2019-10-01'
order by value

Last point: you can decide to insert records with ZERO value, or skip them, or insert them with NULL value. It depends on what is the goal of the table.

Is there any way to order result by column value?

1 Answers