MySQL sorting multiple columns with different sort order

Question

I have a table in which I have three fields with data type date, int and bigint.

I want to sort my select query using all these three columns. I want to sort them all in descending order. For example:

Select * From mytbl 
order by date desc,intnum desc, bigintnum desc;

Is it possible that i could get a result starting from max of all three columns. like latest date, highest intnum and higest bigintnum.

The query you provided looks valid. PS: mysql dba asking sql basics question looks funny ;-) — zerkms
– zerkms, Commented Jul 20, 2011 at 5:24
@Devjosh: so what? It is possible to get the resultset ordered by all fields descendant, started with the highest each column value. — zerkms
– zerkms, Commented Jul 20, 2011 at 5:27
@zerkms, MySQL DBA:yes it's possible :), Nothing wrong with the query our peer posted in question — Devjosh
– Devjosh, Commented Jul 20, 2011 at 5:32
Hi All, Thanks for your comments but what I want this query to do is something like this:- I have a data for ex:- DATE intnum bigintnum 2011-07-18 9 546545 2011-07-16 10 123456 — MySQL DBA
– MySQL DBA, Commented Jul 20, 2011 at 5:55

bash- · Accepted Answer · 2011-07-20 05:50:29Z

2

no

What your query does is get the max date, followed by the max intnum of the max date followed by the max bigintnum of the max intnum of the max date

In other words, your query would not return the maximum value of all three columns

It orders by the date first, then the intnum, then the bigintnum The results would be something like this

2011-07-20    12    14
2011-07-20    12    13
2011-07-20    11    16
2011-07-20    10    12
2011-07-19    20    15
2011-07-18    60    30
2011-07-18    50    14

edited Jul 20, 2011 at 5:50

answered Jul 20, 2011 at 5:38

bash-

6,33410 gold badges45 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bash- Over a year ago

you just misunderstood my answer or you're just confused

MySQL DBA Over a year ago

@bash :- Yes you are right thats the common behaviour of order by clause but I would want it the way i said ..... any idea using any other way i can do it??? thanks

bash- Over a year ago

@MySQLDBA I don't quite understand what you want to do. Are you trying to get 3 columns where the sort is in DESC order with no relation to the other columns? Such as this in relation to the one in my answer? 2011-07-20 60 30 2011-07-20 50 16 2011-07-20 20 15 so basically ordering them all separately?

bash- Over a year ago

@MySQLDBA Why don't you update your question and type out what you are trying to output.

Sharad · Accepted Answer · 2013-01-08 12:14:15Z

1

It is not possible to get a result starting from max of all three columns. like latest date, highest intnum and higest bigintnum.

 Select * From mytbl 
 order by date desc,intnum desc, bigintnum desc;

As you know what ORDER BY does, if you have multiple columns in order by clause, It will first order by DATE Desc then for the very first Date it will order by INTNUM Desc and then order by BIGINTNUM.

answered Jan 8, 2013 at 12:14

Sharad

3,5786 gold badges41 silver badges60 bronze badges

Collectives™ on Stack Overflow

MySQL sorting multiple columns with different sort order

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related