jump to content

Top 3 ranks' SQL for Oracle

Satheesh Babu
2001/02/01

Finding the top N ranks in a query was always an interesting problem with Oracle. Here's some SQL to help you do that quickly.

A much more efficient method is to get it in a cursor and read only 3 records.

Query: 

  SELECT A.studentid, A.grade
    FROM student_grades A
    WHERE 3 >= (SELECT COUNT(DISTINCT B.grade)
                 FROM student_grades B
                 WHERE b.grade >= a.total_amount)
    ORDER BY A.grade DESC

  STUDENTID        GRADE 
  --------- ------------
  482                300
  650                142
  1481               109

  3 rows selected.

From Oracle 8 onwards, this is a non-issue since FROM clause can have sub-queries.