oracle display customer who purchased most cars WITHOUT analytic functions

Question

I'm currently trying to answer the following question:

Display the name of the customer who has purchased the most cars from Archie’s Luxury Motors.

Tables I'm working with:

Customer

(custID, name, DOB, streetAddress, suburb, postcode,
gender, phoneNo, email, type)

SalesTransaction

(VIN, custID, agentID, dateOfSale, agreedPrice)

My query

select * 
from (
select customer.name
from customer, salestransaction
where customer.custID = salestransaction.custID
group by (customer.name), salestransaction.custID
order by count(*) desc
)
where rownum=1;

Now I've found out that I cannot use analytic functions (rownum & rank)

How would I go about doing this with using pure transactional SQL only?

`rownum` isn't a function. Given that Oracle has no `LIMIT` or `TOP` clauses, I don't see another way — Phil, May 21 '15 at 04:59
Seems I spoke too soon ~ http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering/26051830#26051830 — Phil, May 21 '15 at 05:01
rownum is indeed a function. source: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions156.htm#SQLRF06100 — Rodi Marcus, May 21 '15 at 05:03
`ROW_NUMBER()` is a function. `ROWNUM` is a pseudo-column, These are different things. — APC, May 21 '15 at 05:17
"*I cannot use analytic functions*" - why? "*in pure transactional SQL only*" - window functions **are** "pure" SQL (they have been part of the SQL standard for over 10 years now) — a_horse_with_no_name, May 21 '15 at 08:32

Lalit Kumar B · Accepted Answer · 2015-05-21T08:17:43.730

0

You could use MAX and COUNT aggregate functions:

WITH data AS
  (SELECT c.name cust_nm,
    COUNT(*) cnt
  FROM customer c,
    salestransaction s
  WHERE c.custID = s.custID
  GROUP BY c.NAME
  ORDER BY cnt DESC
  )
SELECT cust_nm FROM data WHERE cnt =
  (SELECT MAX(cnt) FROM DATA
  );

An example from EMP and DEPT table:

SQL> WITH data AS
  2    (SELECT e.deptno,
  3      COUNT(*) cnt
  4    FROM emp e,
  5      dept d
  6    WHERE e.deptno = d.deptno
  7    GROUP BY e.deptno
  8    ORDER BY cnt DESC
  9    )
 10  SELECT deptno FROM DATA WHERE cnt =
 11    (SELECT MAX(cnt) FROM DATA
 12    );

    DEPTNO
----------
        30

SQL>

edited May 21 '15 at 08:17

answered May 21 '15 at 05:12

Lalit Kumar B

43,345
10
82
112

That makes sense. Thank you. One more thing, how would I make it so it doesn't output the count? – Rodi Marcus May 21 '15 at 07:27
@user2959072 Instead of `select *` do `select c.name from...` Please mark it answered, would help others. – Lalit Kumar B May 21 '15 at 07:34
I have tried that, but it results in a 'c.name invalid identifier' – Rodi Marcus May 21 '15 at 07:58
@user2959072 That's because alias needs to be provided. I have updated the query, check it. – Lalit Kumar B May 21 '15 at 08:18

oracle display customer who purchased most cars WITHOUT analytic functions

My query

1 Answers1