How to only display top 2 rows?

Question

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

What happens if there are more than 2 branchid's sharing the top two avg(sessionprice)? Do you still only want to display 2 rows? — Boneist
– Boneist, Commented Oct 3, 2016 at 10:59
If this is for a homework question, then it's not so much of an issue. If it's for reporting of a production system, then it becomes more important. If you really do only care about the first two rows, and you do have a case where you have 2 (or more) sharing one of the top two average session prices, do you care if the branchids reported by your query could be different from run to run? Because if you do care, then you should introduce additional elements in your order by clause to ensure the ordering is fixed when ties (aka draws) occur. E.g. ORDER BY AVG(SESSIONPRICE) DESC, branchid asc — Boneist
– Boneist, Commented Oct 3, 2016 at 11:24

Chandana Kumara · Accepted Answer · 2016-10-03 10:33:07Z

7

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

answered Oct 3, 2016 at 10:33

Chandana Kumara

2,6651 gold badge25 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kana Ki · Accepted Answer · 2016-10-04 14:41:09Z

In newer versions of Oracle (12c and above) 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 to 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.

YES. The second example: using rownum worked! Thanks a lot. Now that i see it, I shouldve known! Drats. Thanks again!

jarlh · Accepted Answer · 2016-10-03 10:36:12Z

5

More recent Oracle versions have FETCH FIRST:

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

For older Oracle versions, useROWNUM. Probably something like:

select * from
(
    SELECT BRANCHID, AVG(SESSIONPRICE)
    FROM SESSIONS 
    GROUP BY BRANCHID
    ORDER BY AVG(SESSIONPRICE) DESC
) dt
WHERE ROWNUM <= 2

edited Oct 3, 2016 at 10:36

answered Oct 3, 2016 at 10:27

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

5 Comments

Roy123 Over a year ago

didn't oracle also have rownum? like so: WHERE ROWNUM <= 2

Alonzo Robbe Over a year ago

This gives me an "SQL command not ended properly" starting at the row and line of the FETCH FIRST

jarlh Over a year ago

Seems like your Oracle version is too old for FETCH FIRST. Check out rownum instead.

Sankar Over a year ago

@jarlh i think you have to include ; in the end

Alonzo Robbe Over a year ago

@jarlh I have checked out rownum also, and every time it gives me either an "Group By" or "order by" error

stefan_ah · Accepted Answer · 2016-10-03 10:35:21Z

2

Use SQL LIMIT command to limit the number of results or SQL TOP if not using MySQL.

Both do the same thing.

Use SQL LIMIT command to limit the number of results or SQL TOP if not using MySQL.

Both do the same thing.

with TOP

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

answered Oct 3, 2016 at 10:35

stefan_ah

544 bronze badges

Comments

Raunak Gupta · Accepted Answer · 2016-10-03 10:39:37Z

2

UPDATED

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

Reference:

edited Oct 3, 2016 at 10:39

answered Oct 3, 2016 at 10:31

Raunak Gupta

10.8k4 gold badges62 silver badges104 bronze badges

2 Comments

Alonzo Robbe Over a year ago

This gives me an "SQL is not properly ended" error. Starting at the line of FETCH FIRST

Raunak Gupta Over a year ago

@RobBor: check this query

Collectives™ on Stack Overflow

How to only display top 2 rows?

5 Answers 5

Comments

1 Comment

5 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

5 Comments

Comments

2 Comments

Related