-1

I'm trying to display just the top two rows in Oracle, however I've not been able to come across anything that works.

I currently have 

SELECT BRANCH.BRANCHID, BRANCHNAME, AVG(SESSIONPRICE)
FROM BRANCH, SESSIONS
WHERE SESSIONS.BRANCHID = BRANCH.BRANCHID
GROUP BY BRANCHNAME, BRANCH.BRANCHID
ORDER BY AVG(SESSIONPRICE) DESC

Which returns this

B2      A     18.67
B4      B     17.57
B3      C     15.44
B1      D     13.99

However, I only want the top two rows of this table.

How on earth do I do this?

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
E. Pluess
  • 5
  • 2
  • `fetch first 2 rows only` https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABBADDD – a_horse_with_no_name Oct 05 '16 at 10:39
  • 1
    Have you tried [searching](http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering)? – Aleksej Oct 05 '16 at 10:58
  • Yeah I have and I couldn't find a solution, I've only been learning SQL for like 2 weeks. So a lot of it I don't understand. – E. Pluess Oct 05 '16 at 11:05

3 Answers3

0

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the TOP N rows. Examples:

Get the top 2 BRANCH using RANK()

SELECT ename, sal 
  FROM ( SELECT BRANCH.BRANCHID, BRANCHNAME, AVG(SESSIONPRICE) RANK() OVER (ORDER BY AVG(SESSIONPRICE) DESC) avg_rank
           FROM BRANCH) 
 WHERE avg_rank<= 2;

Get the top 2 BRANCH using DENSE_RANK()

SELECT ename, sal 
  FROM ( SELECT BRANCH.BRANCHID, BRANCHNAME, AVG(SESSIONPRICE) DENSE_RANK() OVER (ORDER BY AVG(SESSIONPRICE) DESC) avg_Dense_rank
           FROM BRANCH) 
 WHERE avg_Dense_rank<= 2;
MANISH KUMAR CHOUDHARY
  • 3,144
  • 2
  • 20
  • 30
-1

I had edited answer again:

SELECT *
FROM (SELECT BRANCH.BRANCHID, BRANCHNAME, AVG(SESSIONPRICE)
FROM BRANCH, SESSIONS
WHERE SESSIONS.BRANCHID = BRANCH.BRANCHID

GROUP BY BRANCHNAME, BRANCH.BRANCHID
ORDER BY AVG(SESSIONPRICE) DESC
)
WHERE ROWNUM <= 2;
Pritam Parashuram Thakur
  • 113
  • 9
-2

Use RowNUM :

It is used to specify the number of records to return.

            SELECT BRANCH.BRANCHID, BRANCHNAME, AVG(SESSIONPRICE)
            FROM BRANCH, SESSIONS
            WHERE 
            ROWNUM <= 2 and SESSIONS.BRANCHID = BRANCH.BRANCHID
            GROUP BY BRANCHNAME, BRANCH.BRANCHID
            ORDER BY AVG(SESSIONPRICE) DESC ;
Mr. Bhosale
  • 2,603
  • 1
  • 12
  • 31