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

Reputation: 1171

SQL Order By clause with group

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?

Upvotes: 2

Views: 148

Answers (3)

wvdz
wvdz

Reputation: 16641

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

Upvotes: 1

D Stanley
D Stanley

Reputation: 152501

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 4

Related Questions