1

I have table.

Table structure is

enter image description here

And now I run the query

SELECT * FROM `studentregistrations`
ORDER BY `studentregistrations`.`studentID`  DESC, `studentregistrations`.`studentName`

And the result I am getting is

enter image description here

I want an explanation how it is working. Because I am confusing that it should give result like studentID is in descending order and studentName is in ascending order.

I checked below answer but not getting any proper explanations

mysql query order by multiple items

PHP MySQL Order by Two Columns

Community
  • 1
  • 1
urfusion
  • 5,528
  • 5
  • 50
  • 87
  • 3
    Here you are **first** ordering by descending ID, and then by name in case the id is the same. As the id can't be the same, the second ordering is useless. – Brewal Feb 03 '16 at 10:51
  • "...it should give result like studentID is in descending order and studentName is in ascending order." — write your expectations, how do you expect this output to be. It's a database, every row is consistent, every field belongs to one certain row. But looks like you want to have every column separately. Then it's not a DB task. – ksimka Feb 03 '16 at 10:56
  • @ksimka : yep. You are right. I also think so but when reading the answers which i mention in my answer I think this query will also work. – urfusion Feb 03 '16 at 10:58
  • 1
    No, it will not. Let's say you have a table with alphabet, one column for number, another - letter. So normally it looks like 1-A, 2-B, 3-C, 4-D, ... And suddenly you want to order it like in your question. What do you expect? 23-A, 22-B, 21-C, ...? What kind of data is this? What is it for? You break consistency. – ksimka Feb 03 '16 at 11:03
  • @ksimka: yes. Got your point. – urfusion Feb 03 '16 at 11:04
  • And a little bit more to understand: you order the whole **records**, not separate *fields* of **record**. – ksimka Feb 03 '16 at 11:04

3 Answers3

5

I think your expectations are that the columns are sorted independently, so that all student names are in alphabetic order and all student ids are in descending order, independently on the names. If that would happen, you would get results where a student id is next to the wrong name, so fortunately that doesn't happen.

Instead, it sorts by the first column first and then by the next column. The secondary sort only applies to groups that have the same value in the first column.

So if you would have 10 students with the same ID, then for that ID their names would be alphabetically sorted. But since the ID is unique, the secondary sorting is useless.

It would be useful, for example, to use 

ORDER BY UniversityId, StudentName

That way, you would have a list where all students of the same university are grouped together, and within these groups they are sorted alphabetically by name.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
3

When you sort using multiple columns, the order of the second column only influences the order when two or more values are equal in the first column. If all values in the first column are unique, the other order columns do not matter.

Patrick Kostjens
  • 5,065
  • 6
  • 29
  • 46
1

Your query first orders by student id, and then following that, orders any 'ties' by student name.

It's impossible to have both of your order by's honoured in full, because then there would be row/column mismatches.

Adam Copley
  • 1,495
  • 1
  • 13
  • 31