Programs Knowledge

Grade_table

方法一:

Select * From

     (Select SUBJECT, STUDENT_ID, GRADE, ROWNUM as RN From

           (Select SUBJECT,STUDENT_ID, GRADE From Grade_table

            Where SUBJECT = ‘Mathematics’

            Order by GRADE Desc

           )

      Where ROWNUM < 4 --取前三名

      Order by GRADE Desc

     )

Where RN = 3 --只列第三名

方法二:

Select * From

     (Select SUBJECT, STUDENT_ID, GRADE,

RANK() OVER(PARTITION BY SUBJECT Order by GRADE Desc) as RANK

From Grade_table

      Where SUBJECT = ‘Mathematics’

     )

Where RANK < 4 

Result(紅色部分):

Result(紅色部分+綠色部分):