-3

Good morning, I need assistance with generating a top 10 list. below is the created query however i'm unsure how to correctly implement the ROWNUM function.

SELECT * FROM
( SELECT CON_NAME, HIGHEST_QUAL FROM temp2 ORDER BY HIGHEST_QUAL DESC )
  WHERE ROWNUM = 10;

As when the query is run no data is produced however when i omit the ROWNUM all the rows are produced showing the data is there. Also when the ROWNUM is set to 1 only one row is produced.

Thanks in advance!

user8960350
  • 1
  • 2

1 Answers1

0

Use ROWNUM inside the subquery and reuse the aliased name with conditions as you like

TOP 10 Highest

   SELECT * FROM
    ( SELECT ROWNUM rnum, CON_NAME, HIGHEST_QUAL FROM temp2 ORDER BY HIGHEST_QUAL DESC )
      WHERE rnum <= 10;

10th Highest

   SELECT * FROM
    ( SELECT ROWNUM rnum, CON_NAME, HIGHEST_QUAL FROM temp2 ORDER BY HIGHEST_QUAL DESC )
      WHERE rnum = 10;

Greater Than Fifth Highest, lesser than 9th Highest

   SELECT * FROM
    ( SELECT ROWNUM rnum, CON_NAME, HIGHEST_QUAL FROM temp2 ORDER BY HIGHEST_QUAL DESC )
      WHERE rnum > 5 and rnum < 9;
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45