title: List the top ten employees from each department
body: <p>How can I list the top ten employees from each department in order and each department the employees should be ordered according to the total amount of purchases?</p>
  
  <p>These are the tables:</p>
  
  <pre><code>CREATE TABLE EMP (
      EMPNO NUMBER(4),
      ENAME VARCHAR2(20),
      POSITION VARCHAR2(20),
      DEPTNO NUMBER(2)
  );
  
  CREATE TABLE DEPT (
      DEPTNO NUMBER(2),
      DNAME VARCHAR2(20)
  );
  
  CREATE TABLE CLIENT (
      CLIENTNO NUMBER(5),
      CNAME VARCHAR2(20),
      PHONE VARCHAR2(10)
  );
  
  CREATE TABLE PURCHASE (
      PURCHASENO  NUMBER(5),
      RECEIPTNO   NUMBER(6),
      SERVICETYPE VARCHAR2(25),
      PAYMENTTYPE VARCHAR2(10),
      GST     VARCHAR2(3),
      AMOUNT      NUMBER(4),
      SERVEDBY    NUMBER(4),
      CLIENTNO    NUMBER(5)
  );
  </code></pre>
  
  <p>Edited with attempt:</p>
  
  <pre><code>SELECT DISTINCT *
  FROM (SELECT E.ENAME, D.DEPTNO, COUNT(P.PURCHASENO) AS TOTALAMOUNT,
               AVG(P.AMOUNT)AS AVGAMOUNT, MAX(P.AMOUNT)AS HIGHESTAMOUNT,
               SUM(P.AMOUNT)AS TOTALAMOUNTPURCHASE,
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY AMOUNT DESC)RANK
        FROM EMP E, DEPT D, PURCHASE P
       )
  WHERE RANK&lt;=10
  </code></pre>