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
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
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
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.
orderworks on a row basis. e.g.order by a,b,c. only when multiple rows inahave the same value will it start ordering byb, and then only start oncwhen multiple values of b are encountered.order bywill NOT rearrange values so that they change the rows they're in.