-1

I have an oracle sql database consisting of three tables and I was wondering, What is the most efficient subquery that can be written to retrieve the information of the customer stored in the table customer_info who has performed the highest amount of purchases in total.(The purchase data is in the table purchase_logs). i.e the number of transactions one customer has performed NOT the quantity of the items purchased. i.e my aim is to retrieve the customer details of the customer witht he highest amount of purchases done. I have 3 tables one for the customer_info, one as the purchase_logs and the last one being the item_info.

My current Approach

SELECT * FROM customer_info
WHERE customer_id = (SELECT cust_id
    FROM purchase_logs 
    GROUP BY cust_id
    ORDER BY COUNT(*)
    DESC LIMIT 1);

This doesn't seem to give me any results at all unfortunately.

This is my Database Schema along with the Sample Data of purchase_logs, customer_info, item_info and the Expected Output

I would really appreciate any help in understanding what the proper approach to solving this problem would be.

Baka Human
  • 7
  • 3
  • Are you looking for highest amount of purchases or highest number of purchases. Your question has 'highest amount', but the example you show does count(*) ? – Pankaj May 17 '22 at 22:40
  • highest number of purchase i.e the number of transactions one customer has performed not the quantity of the items purchased. – Baka Human May 17 '22 at 22:42
  • Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – philipxy May 17 '22 at 22:57
  • 1
    [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [mre] Please clarify via edits, not comments. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) Google many clear concise precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. [ask] [Help] – philipxy May 17 '22 at 23:00

1 Answers1

0

There is no limit 1 in Oracle SQL, use row limiting clause instead (fetch first in the example below):

SELECT * 
FROM 
    (SELECT cust_id, count(*) cnt
     FROM purchase_logs 
     GROUP BY cust_id
     ORDER BY cnt desc
     fetch first 1 row only with ties
    ) vc
    join customer_info
        on customer_id = vc.cust_id;
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • This gives me the error that I'm missing a right parenthesis ` fetch first 1 row only with ties * ERROR at line 7: ORA-00907: missing right parenthesis` , I've noticed this a few times whenever I try to use order by in a subquery. What should I be doing differently ? – Baka Human May 18 '22 at 07:25
  • Without create table statements , test data and expected outcome is difficult for people to help and test. This is the reason images are discouraged as people aren't going to spend time settling up your data. Convert the images to data and people will be amenable to help you solve your problem – Pugzly May 19 '22 at 18:23
  • Ah I see, will keep that in mind. thank you – Baka Human May 25 '22 at 22:30