CODEWITHSUNDEEP
sqlsql-serversql-order-by
Kimchi Man
Kimchi Man

Reputation: 1171

Order By clause on multiple fields with group

I am learning SQL.

I have these records in my table:

STUDENT_NAME    STATUS    CLASS_ID    ORDERING
----------------------------------------------
        Adam      'FR'          10           1
       Conan      'SE'           3           5
       Chris      'SE'           4           8 
       Louis      'JR'        null          10
        Dave      'JR'          14          17
       Alice      'SO'          14           8
        Paul      'SE'           4           6
         Tom      'SO'        null           3
        Eric      'FR'          14           5
       Blake      'JR'          11           2
        Ryan      'SE'           3          13
        Matt      'FR'        null          22

And I want to order by ORDERING, but I want to group by its STATUS (like the value which has same status be together).

For example, the result would look like this:

STUDENT_NAME    STATUS    CLASS_ID    ORDERING
----------------------------------------------
        Adam      'FR'          10           1
        Eric      'FR'          14           5
        Matt      'FR'        null          22
       Blake      'JR'          11           2
       Louis      'JR'        null          10
        Dave      'JR'          14          17
         Tom      'SO'        null           3
       Alice      'SO'          14           8
       Conan      'SE'           3           5 // SAME CLASS_ID
        Ryan      'SE'           3          13
        Paul      'SE'           4           6 // SAME CLASS_ID
       Chris      'SE'           4           8

Adam has lowest ORDERING value, so he goes the top. But his STATUS is 'FR', so the next student will be Eric who has lower ORDERING than Matt.

EXTRA CONDITION: If CLASS_ID is not null, I want to group it based on CLASS_ID

Is it even possible to get the result with the clause 'SELECT'...'ORDER BY'? (Please say yes!)

I use this query, but it doesn't work well: 

SELECT ... FROM STUDENTS stu
ORDER BY 
(SELECT MIN(S.ORDERING) FROM STUDENTS S WHERE S.STATUS = 'FR' AND stu.STATUS = 'FR'),
(SELECT MIN(S.CLASS_ID) FROM STUDENTS S WHERE S.CLASS_ID = stu.CLASS_ID),
stu.CLASS_ID ASC,
stu.ORDERING ASC

I could use the CASE clause too. I need your help!

Upvotes: 0

Views: 75

Answers (3)

FuzzyTree
FuzzyTree

Reputation: 32392

It sounds like you want to order by the minimum of each group

select s.* from students s
join (
    select status, min(ordering) min_status_ordering 
    from students group by status
) t1 on t1.status = s.status
join (
    select class_id, status, min(ordering) min_class_ordering 
    from students group by status, class_id
) t2 on (t2.class_id = s.class_id 
  or (t2.class_id is null and s.class_id is null))
  and t2.status = s.status                                  
order by t1.min_status_ordering, t2.min_class_ordering, s.ordering, s.status, s.class_id

http://sqlfiddle.com/#!2/f4eb4/2

Upvotes: 1

Tom
Tom

Reputation: 6663

Try this. I wrote this for MySQL, it may work for SQL Server also. It will group NULL CLASS_IDS together (if there are any). I don't can't think of another way to do it. It gives the results you are looking for.

SELECT stu.*

FROM STUDENTS stu

ORDER BY 
(SELECT MIN(S.ORDERING) FROM STUDENTS S WHERE S.STATUS = stu.STATUS),
(SELECT MIN(S.ORDERING) FROM STUDENTS S WHERE S.STATUS = stu.STATUS AND IFNULL(S.CLASS_ID, '') = IFNULL(stu.CLASS_ID, '')),
stu.ORDERING

SQL Fiddle: sqlfiddle.com/#!2/a5a10/10/0

Upvotes: 2

iSWORD
iSWORD

Reputation: 808

You can simply do

SELECT * FROM students
ORDER BY STATUS, ORDERING, CLASS_ID

Here's a fiddle.

Upvotes: 2

Related Questions