CODEWITHSUNDEEP
sqloracle-database
hatorihanso
hatorihanso

Reputation: 59

LIMIT operator and error: ORA-00933: SQL command not properly ended

I have the table with columns ClientID NUMBER (15,0), ProductPrice NUMBER (15,0), ProductQuantity NUMBER (21,0), ProductName VARCHAR2(50), BuyDate DATE

I do the script below to extract the ProductName, on which the each customer spent more money:

SELECT ClientID, MAX(ProductPrice * ProductQuantity) AS ForProduct
FROM TESTVIEW 
WHERE BuyDate BETWEEN TO_DATE('01.01.2020','MM.DD.YYYY') AND TO_DATE('01.05.2020','MM.DD.YYYY')
GROUP BY ClientID
ORDER BY ClientID, ForProduct DESC LIMIT 1

However, I get error ORA-00933: SQL command not properly ended. Without operator LIMIT it works correctly. What is the reason and how could I get the result that I need?

Upvotes: 0

Views: 336

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Based on your comment, use ROW_NUMBER():

SELECT t.*
FROM (SELECT ClientID, MAX(ProductPrice * ProductQuantity) AS ForProduct,
             ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY MAX(ProductPrice * ProductQuantity) DESC) as seqnum
      FROM TESTVIEW 
      WHERE BuyDate BETWEEN DATE '2020-01-01' AND DATE '2020-01-05'
      GROUP BY ClientID
     ) t
WHERE seqnum = 1;

Upvotes: 1

Related Questions