mysql multiple column sorting order

Question

I have 1 table with 6 columns all are tinyint with 1 digit. I need a query to return the data sorted (ordered) desc in each column.

example:

col1    col2    col3
  1       2       5
  1       7       3
  2       3       7

expected result:

  2      7        7
  1      3        5 
  1      2        3

I tried order by col1, col2 DESC but it only affects the first column (maybe because it's from the same table?) thx, Danny

there is no such row with values 2,7,7 in your original data. — Randy, Sep 24 '13 at 17:26
not possible. `order` works on a row basis. e.g. `order by a,b,c`. only when multiple rows in `a` have the same value will it start ordering by `b`, and then only start on `c` when multiple values of b are encountered. `order by` will NOT rearrange values so that they change the rows they're in. — Marc B, Sep 24 '13 at 17:27
i think 'not possible' is extreme @MarcB :) It is just a very odd request and certainly not going to happen with a regular ORDER BY clause. — Randy, Sep 24 '13 at 17:29
@randy: you can't get a DB to mangle up records so that values from multiple rows suddenly shift the record they're in to appear elsewhere... not with a simple query like this. — Marc B, Sep 24 '13 at 17:30
@MarcB exactly - it is very odd... but some hack like my answer below might work — Randy, Sep 24 '13 at 17:32

score 2 · Answer 1 · answered Sep 24 '13 at 17:31

2

maybe something like this:

select col1, col2, col3
from
( select row_number() r, col1 from mytab order by col1 desc ) a,
( select row_number() r, col2 from mytab order by col2 desc ) b,
( select row_number() r, col3 from mytab order by col3 desc ) c
where a.r = b.r
and a.r = c.r

answered Sep 24 '13 at 17:31

Randy

16,480
1
37
55

what does row_number means...? – Danny Valariola Sep 24 '13 at 17:34
yes - rownum in Oracle...actually not exactly sure the syntax in mysql - but edited answer. – Randy Sep 24 '13 at 17:36
I dont get it...no such column row_number + FUNCTION row_number does not exist – Danny Valariola Sep 24 '13 at 17:36
@Randy Apparently it doesn't exist in MySQL! See: http://stackoverflow.com/questions/1895110/row-number-in-mysql – Eric Hotinger Sep 24 '13 at 17:39
Exactly what I did, but was looking for something which can do it in a single query! I guess it's not possible! – Anupam Apr 01 '14 at 07:06

score 1 · Accepted Answer · answered Sep 24 '13 at 17:35

I am assuming that you are getting the data from the same table, which is what's causing you the problem, because, when saying orderby, the db engine assumes that the row data is consistent and should not be split, so it orders only with the first selector, which is col1 in your case. the solution is, to acquire each column by it's own, ordered, in a separate query, and then, you'll get your result. so, you will end up, in the simple way of doing it, with three queries:

select col1 from table orderby col1 desc;

select col2 from table orderby col2 desc;

and so on

take it easy, the worse things are the small ones :D – Labib Ismaiel Sep 24 '13 at 17:58 — Labib Ismaiel, Sep 24 '13 at 17:58

score 0 · Answer 3 · edited Aug 24 '14 at 18:40

In a query, you can use multiple order by. But you can't get expected result. Because the mysql will order based on its preference. ie, mysql ordered the columns from left to right. Suppose your query like this:

select * from table order by col1 asc, col2 desc

Where, the mysql first ordered col1 in ascending order and display the result. Then it ordered col2 in descending order. So the result of 2nd order by is not displayed correct. It displayed only based on order by result. Finally you cannot get answer as you expect.

mysql multiple column sorting order

3 Answers3