Rob Bor Rob Bor - 2 months ago 6
SQL Question

How to only display top 2 rows?

I have spent the last 2 hours searching for this, and every thing I have tried has not worked. I have a table_Sessions for which I want to return a BranchID and Average cost of each session. But I only want to show the top 2 averages.

I have literally tried everything i have come across. either it doesnt fit my case, or I have appropriated it wrong. Either way, I'm going crazy here.

Here is what I have so far, and it seems to do about 80% of what I want it to (last 20% being only show the top 2 highest average costs)

SELECT BRANCHID, AVG(SESSIONPRICE)
FROM SESSIONS
GROUP BY BRANCHID
ORDER BY AVG(SESSIONPRICE) DESC;


If someone could PLEASE tell me how to append to it, I would greatly be appreciative of it. I am using Oracle SQL Developer.

Thanks

Answer

In newer versions of Oracle you can use Fetch First, i.e.:

SELECT BRANCHID, AVG(SESSIONPRICE)
FROM SESSIONS 
GROUP BY BRANCHID
ORDER BY AVG(SESSIONPRICE) DESC
FETCH FIRST 2 ROWS ONLY;

In elder versions you can use rownum. However for this we need to make sure your ordering is evaluated first, so you need to put your existing query inside a sub-query, then add the WHERE clause that to filter by rownum.

For example:

SELECT * FROM
    (SELECT BRANCHID, AVG(SESSIONPRICE)
    FROM SESSIONS 
    GROUP BY BRANCHID
    ORDER BY AVG(SESSIONPRICE) DESC) myQuery
WHERE rownum <= 2;

See https://www.techonthenet.com/oracle/questions/top_records.php for further explanation of rownum.