CODEWITHSUNDEEP
mysqlsql
Parveez Ahmed
Parveez Ahmed

Reputation: 1417

Is it possible to order by two fields in a given table,one in ascending and the other in descending order at the same time?

Recently, I have come across a question that has been asked in an interview which states that:

You have mysql database with a table students. Write a query string to select all the items of the table students and order by two fields one ascending and the other descending.

Let's have a table "students" for example:

enter image description here

From this example, if we order by Score in descending order then there is no way to order by roll_no in ascending order at the same time.

From the point of view of the question, can there be written any query to obtain the desired result Or is the question ambiguous or wrong or my approach to the understanding of the question is wrong?

Upvotes: 0

Views: 237

Answers (2)

kiran gadhe
kiran gadhe

Reputation: 743

In your order by you have asked system to order by First column first so it have ordered then you have asked it to order by second column so it have

-> It have to keep ordering of first column.

-> Order by second column too

So it does ordering within group means if 

Table Test 
 A| B
 --------
  1  1
  1  3
  1  2
  2  2 
  2  4


Select * from test order by A desc, B asc 
Output 
Table Test 
 A| B
 --------
 2  2 
 2  4
 1  1
 1  2
 1  3

So in your case, if you first order by score desc then it will order

  1. First, all students according to there scores descending

  2. And then if two or more students have the same score say 60 then within that group the students will be ordered according to roll number ascending

Hope this clears your doubt.

Upvotes: 0

Kobi
Kobi

Reputation: 2524

When you use the order by clause, you can specifiy the direction Asc or desc.

In your example with your sample data, there is no point of order by score, then by roll_no, because there is no duplicate in the score column.

If in your real table there are score wich appeared more than once, you can order by score desc, roll_no.
( asc is the default value)

Upvotes: 0

Related Questions