CODEWITHSUNDEEP
sqloracle-sqldeveloper
Lachlan Doherty
Lachlan Doherty

Reputation: 5

Attempting to display top 2 average prices

I am trying to display the top 2 average prices, however I am having an issue as the two columns I need to display are from different tables and the join is giving me error messages when I attempt solutions from other threads. For example:

Customer Table          Order Table
CID,CNAME               OID,CID,OPRICE    
1,  JOHN                 1,  1,  2.50
2,  JEFF                 2,  1,  3.40
3,  ROB                  3,  2,  4.20
                         4,  3,  3.50

This is what I have got so far but it displays all the results not just the top 2.

SELECT CNAME,AVG(OPRICE)
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CID=ORDER.CID
GROUP BY CNAME;

The result I am after would be:

CNAME,AVG(OPRICE)
JEFF,  4.20
ROB,   3.50

Upvotes: 0

Views: 63

Answers (2)

jarlh
jarlh

Reputation: 44786

More recent Oracle versions have FETCH FIRST:

SELECT CNAME,AVG(OPRICE) as AVGPRICE
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CID=ORDER.CID
GROUP BY CNAME
ORDER BY AVGPRICE DESC
FETCH FIRST 2 ROWS ONLY

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

First, learn proper join syntax. Never use a comma in a FROM clause. Always use JOIN and ON.

Then, in Oracle 12C+, you can do:

SELECT c.CNAME, AVG(o.OPRICE)
FROM CUSTOMER c JOIN
     ORDER o
     ON c.CID = o.CID
GROUP BY c.CNAME
ORDER BY AVG(o.OPRICE) DESC
FETCH FIRST 2 ROWS ONLY;

Earlier versions require a subquery:

SELECT CNAME, avg_oprice
FROM (SELECT c.CNAME, AVG(o.OPRICE) as avg_oprice
      FROM CUSTOMER c JOIN
           ORDER o
           ON c.CID = o.CID
      GROUP BY c.CNAME
      ORDER BY AVG(o.OPRICE) DESC
     ) c
WHERE rownum <= 2;

Upvotes: 1

Related Questions