CODEWITHSUNDEEP
mysql
Bharat
Bharat

Reputation: 1

How to select MySQL rows order by highest values from any column?

I have a MySQL table of 2000 columns and 120000 rows (numerical values). I wanna select the some rows based on highest value from all 2000 columns.

select * from table where column1='x' ORDER BY all_columns DESC;

Basically I wanna order multiple columns based on highest value of any column not one-by-one.

Upvotes: 0

Views: 473

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I suspect you may want this:

select t.*
from table t
order by greatest(col1, col2, col3, . . . ) desc
limit 1;

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116100

You can specify a column number in order by, so you should be able to do something like:

select * from table 
where column1='x' 
ORDER BY 1 DESC,2 DESC,3 DESC,4 DESC ..... 1999 DESC,2000 DESC;

I don't think there is a shorter way, but using the right equipment, it should be doable. I am very curious about your usecase, though. This is an extreme number of columns, and most certainly extreme to sort by all of them.

Upvotes: 0

Related Questions