CODEWITHSUNDEEP
sqloracle11g
Mavese
Mavese

Reputation: 131

How to get the highest counted column from count return

So I just started working with SQL and I have a pretty simple problem that I can't figure out. I'm working on a practice database and I want to take a small table of books that have been ordered and find the book that has been ordered the most. So I started like this:

select ISBN, count(ISBN)
from orderitems
group by ISBN;

So this gives me a return of 2 columns the ISBN and how many times that ISBN has been in an order. From here I just want to take the highest ordered ISBN and pass it to and outside query. I thought the best way to find the most ordered ISBN would be to nest the count function inside the max function but I guess it doesn't work like that because I keep getting an error when I try to do it like this:

select ISBN, max(count(ISBN))
from orderitems
group by ISBN;

So my question is what would be the most efficient way to write this query using the max and count functions and sub queries so I will get just the most ordered ISBN returned to my outside query. Thank you for any help.

Upvotes: 0

Views: 37

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Step by step (on concept level):

  1. Count how many times each ISBN appears in a table.
  2. Order your data in a descending manner using values calculated in Step 1
  3. Limit your output to only return first row.

Actual query would look like this :

Since Oracle 12c this can done with FETCH FIRST ... ROWS ONLY syntax

SELECT
  ISBN
FROM
  orderitems
GROUP BY ISBN
ORDER BY COUNT(ISBN) DESC
FETCH FIRST 1 ROWS ONLY

For Oracle versions prior to 12c you could for example use a ROWNUM pseudocolumn

SELECT *
FROM (
  SELECT
    ISBN
  FROM
    orderitems
  GROUP BY ISBN
  ORDER BY COUNT(ISBN) DESC
  )
WHERE rownum = 1

Upvotes: 1

Related Questions