How can I limit the amount of rows returned by an oracle query after ordering?

Question

I know this is a duplicate question asked before but the other post doesn't solve my problem.

I am trying to display only the top 2 rows that result from the following query.

SELECT
    AVG(sessionprice),
    branchsuburb
FROM    branch,
        sessions
WHERE branch.branchid = sessions.branchid
GROUP BY branchsuburb
ORDER BY AVG(sessionprice) DESC;

That query as is will return all the results but I wish to restrict it to only the top 2 (in terms of which row has the highest average). I tried the fetch function in this post How do I limit the number of rows returned by an Oracle query after ordering? however it simply returns a ORA-00933 error. I know I must be making some silly mistake but I can't figure it out. Is there a way to do this without using a subquery?

The code after adding the fetch function looks like this

SELECT
    AVG(SESSIONPRICE),
    BRANCHSUBURB
FROM    BRANCH,
        SESSIONS
WHERE BRANCH.BRANCHID = SESSIONS.BRANCHID
GROUP BY BRANCHSUBURB
ORDER BY AVG(SESSIONPRICE) DESC
FETCH FIRST 2 ROWS ONLY;

FETCH only exists from Oracle 12; Gordon's answer is what you need — Aleksej, Sep 26 '16 at 13:08

score 6 · Answer 1 · answered Sep 26 '16 at 13:00

6

FETCH was recently introduced in Oracle. In older versions, you need to use a subquery:

SELECT bs.*
FROM (SELECT avg(sessionprice), branchsuburb
      FROM branch b JOIN
           sessions s
           ON b.branchid = s.branchid
      GROUP BY branchsuburb
      ORDER BY avg(sessionprice) DESC
     ) bs
WHERE rownum <= 2;

answered Sep 26 '16 at 13:00

Gordon Linoff

1,122,135
50
484
624

Thanks for the help. Is there no way to do it without a subquery? – Hayden null Sep 26 '16 at 13:01
1

@Hayden null: No. You can move it to a cte as XING shows, but it stays a subquery. Or you can switch to Oracle 12c. – Thorsten Kettner Sep 26 '16 at 13:16

score 1 · Answer 2 · answered Sep 26 '16 at 13:06

1

You can use CTE and achieve this;

with tbl as (SELECT avg(sessionprice), branchsuburb
              FROM branch b JOIN
                   sessions s
                   ON b.branchid = s.branchid
              GROUP BY branchsuburb
              ORDER BY avg(sessionprice) DESC )
select tbl.* from tbl
WHERE rownum <= 2;

answered Sep 26 '16 at 13:06

XING

9,138
3
16
34

1

Thanks a lot. This was extremely helpful. – Hayden null Sep 26 '16 at 13:09

score 0 · Answer 3 · answered May 22 '17 at 08:51

Yon can try this. This is for pagination also with start and end position

SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT * FROM table_name ORDER BY column_name) inner_query WHERE rownum <= 20) WHERE rnum >= 1;

Where rownum is no of records and rnum is for start position.

score 0 · Answer 4 · answered May 22 '17 at 15:16

0

select * from (SELECT
    AVG(SESSIONPRICE),
    BRANCHSUBURB
FROM    BRANCH,
        SESSIONS
WHERE BRANCH.BRANCHID = SESSIONS.BRANCHID
GROUP BY BRANCHSUBURB
ORDER BY AVG(SESSIONPRICE) DESC ) where ROWID <= 2

answered May 22 '17 at 15:16

Mehul Akabari

71
4

How can I limit the amount of rows returned by an oracle query after ordering?

4 Answers4