love2code love2code - 22 days ago 7
SQL Question

Simple ordering in sql

I have table with the following data

id | name | passed | rank | Class
1 abc y 1 1
2 xyz y 1 2
3 lmn n 54 1
4 opq n 54 2
5 rst y 2 1
6 uvw y 2 2


What sql query can give me the following result:

id | name | passed | rank | Class
1 abc y 1 1
2 rst y 2 1
4 def y 55 1
3 lmn n 54 1
5 xyz y 1 2
6 uvw y 2 2
7 opq n 54 2



  1. Group by class first all students with class = 1 , then 2 and so on.

  2. the passsed == n should always come by the end and if there are 2 students with passed == n they should be ordered rank wise.

  3. rest of the students with passed == y should be orders rank wise.



Tried:

select id, name, passed, rank, class
from students
ORDER BY passed DESC, rank


This gives :

id | name | passed | rank | Class
1 abc y 1 1
4 xyz y 1 2
2 rst y 2 1
5 uvw y 2 2
3 def y 55 1
6 opq n 54 2
3 lmn n 54 1


So I get passed==n at bottom and rest ordered as per rank.I think the only thing remaining is group by class.

Answer

Can't you just add the order or class first?

select id, 
        name, 
        passed, 
        rank, 
        class
from students
ORDER BY `Class`, 
        passed DESC, 
        rank