Select nth row Oracle SQL

Question

I'm attempting to access the 3rd row from the following query:

SELECT *
FROM (SELECT
  OFFENSEDESC,
  COUNT(*) AS num
FROM
  CRIMEPROFILE

GROUP BY
  OFFENSEDESC
ORDER BY num DESC) o
WHERE rownum = 3

However, it returns no rows, I am uncertain as to why that is, any help is appreciated. NOTE: ORACLE SQL

Likely, the subquery returns less than 3 rows. Did you try to run the subquery alone to see what it returns? — GMB, Apr 14 '20 at 23:07
No it returns 68 rows, oddly, rownum >= 3 works but I only want the 3rd row. Also no matter what index i chose (rownum = n) it returns no rows. — cise, Apr 14 '20 at 23:09
See [how to select even records from a table in oracle?](https://stackoverflow.com/questions/20014495/how-to-select-even-records-from-a-table-in-oracle) for an explanation of why `ROWNUM = 3` doesn't work (or, in that case, why `MOD( ROWNUM, 2 ) = 0` doesn't work; but the explanation can equally work for `3` instead of even rows). The solution is that you need to `ORDER BY` first, generate the `ROWNUM` second and finally filter and do each of those steps in separate nested subqueries. — MT0, Apr 14 '20 at 23:20

score 1 · Answer 1 · answered Apr 14 '20 at 23:11

1

Try this

SELCT
    OFFENSEDESC,
    num
from
(
    SELECT
      OFFENSEDESC,
      COUNT(*) AS num,
      ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as rnk
    FROM
      CRIMEPROFILE
    GROUP BY
      OFFENSEDESC
) val
WHERE rnk = 3

answered Apr 14 '20 at 23:11

zealous

6,861
3
10
30

Select nth row Oracle SQL

1 Answers1