CODEWITHSUNDEEP
mysql
Danny Valariola
Danny Valariola

Reputation: 1118

mysql multiple column sorting order

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

Upvotes: 3

Views: 3036

Answers (3)

php
php

Reputation: 4425

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.

Upvotes: 0

Labib Ismaiel
Labib Ismaiel

Reputation: 1340

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

Upvotes: 1

Randy
Randy

Reputation: 16677

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

Upvotes: 2

Related Questions