In sql how do you print only the most occuring-CodePudding

   SELECT  S.CUSTID, C.NAME, COUNT(S.CUSTID) as "Customer with most Purchases"
   FROM CUSTOMER C, SALESTRANSACTION S
   WHERE C.CUSTID = S.CUSTID
   GROUP BY (S.CUSTID, C.NAME, C.CUSTID) 
   ORDER BY COUNT(S.CUSTID) DESC;

This actually tells us which person made the most purchases. But how can i print only the top row and not have all the other rows below?

CodePudding user response：

You want to fetch the highest ranked row(s) of your intermediate result. If you want to keep your query as is and only add the appropriate clause, use FETCH and a modified ORDER BY clause with RANK for this.

SELECT
  c.custid,
  c.name,
  COUNT(s.custid) AS transaction_count
FROM customer c
LEFT OUTER JOIN salestransaction s ON s.custid = c.custid
GROUP BY c.custid, c.name
ORDER BY RANK() OVER (ORDER BY COUNT(s.custid) DESC)
FETCH FIRST ROW WITH TIES;

I've changed your comma separated join - a syntax that was used thirty years ago - to a proper explicit join.