SQL Order By clause with group

Question

So I have a SELECT query, and the result is like this:

SELECT .... 
ORDER BY SCORE, STUDENT_NUMBER

STUDENT_NAME    STUDENT_NUMBER    SCORE     
----------------------------------------
  Adam              9              69
  Bob              20              76
  Chris            10              77
  Dave             14              77
  Steve             5              80
  Mike             12              80

But I want to order by STUDENT_NUMBER, but I want them to be grouped by the same score:

STUDENT_NAME    STUDENT_NUMBER    SCORE     
----------------------------------------
  Steve             5              80
  Mike             12              80
  Adam              9              69
  Chris            10              77
  Dave             14              77
  Bob              20              76

So now the data is ordered by STUDENT_NUMBER, but if there is the same SCORE, they are grouped (like it is shown in the next row).

Is it possible to do this with the ORDER BY clause?

possible duplicate of [SQL multiple column ordering](http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) — ElGavilan, Aug 20 '14 at 19:27

score 4 · Accepted Answer · answered Aug 20 '14 at 19:32

It seems that the ordering can also be described as ordering by the minimum student number for each score. You would do this using window functions. Here is an example:

select <whatever>
from (select t.*, min(student_number) over (partition by score) as minsn
      from <whatever> t
     ) t
order by minsn, score, student_number asc;

You do ask if this can be done with the order by. I think the answer is "yes", using a subquery. It would look something like this:

select <whatever>
from <whatever> t
order by (select min(t2.student_number)
          from <whatever> t2
          where t2.score = t.score
         ),
         score, student_number;

The second answer is exactly what I was looking for. Thanks! — Kimchi Man, Aug 20 '14 at 19:43

score 1 · Answer 2 · answered Aug 20 '14 at 19:33

1

You could order by the minimum student number with that score, then by student number:

SELECT STUDENT_NAME, STUDENT_NUMBER, SCORE 
FROM Scores s
ORDER BY (SELECT(MIN(STUDENT_NUMBER) FROM Scores WHERE SCORE = s.SCORE) ,
         STUDENT_NUMBER

answered Aug 20 '14 at 19:33

D Stanley

149,601
11
178
240

wvdz · Answer 3 · 2014-08-20T19:39:50.547

1

I believe this solves your curious ordering requirement. Instead of ordering on Student_Number, you order on the minimum Student_Number for a given score.

WITH v AS
(
    SELECT t1.Student_Number, t1.Score,
    (
        SELECT MIN(Student_Number) FROM table1
        WHERE Score = t1.Score
    ) AS MinStudentNumber
    FROM table1 t1
)
SELECT Student_Number, Score FROM v
ORDER BY MinStudentNumber, Score

edited Aug 20 '14 at 19:39

answered Aug 20 '14 at 19:34

wvdz

16,251
4
53
90

SQL Order By clause with group

3 Answers3