CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Hayden null
Hayden null

Reputation: 1

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

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;

Upvotes: 0

Views: 5370

Answers (4)

Mehul Akabari
Mehul Akabari

Reputation: 101

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

Upvotes: 0

Mukesh Rai
Mukesh Rai

Reputation: 9

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.

Upvotes: 0

XING
XING

Reputation: 9886

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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;

Upvotes: 6

Related Questions