# Thread: get top 5 from sorted list

1. Registered User
Join Date
Dec 2011
Posts
6

## Unanswered: get top 5 from sorted list

SELECT b.ISBN,sum(o.quantity) as TOTALQTYSold,avg((RETAIL-COST)/COST*100) AS PERcentPROFIT
FROM BOOKS b, orderitems o
WHERE b.isbn=o.isbn
group by b.isbn
order by TOTALqtysold desc;

here is my sql but need to only show the Top 5 Isbn and the rest of the columns for the top 5 quantity sold books????

thanks,
capriman

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>the rest of the columns for the top 5 quantity sold books????
How do we know what are the "rest of the columns" which should be returned?
Last edited by anacedent; 12-10-11 at 12:33.

3. Registered User
Join Date
Jul 2003
Posts
2,296
read up on how to use analytic functions.

4. Registered User
Join Date
Dec 2011
Posts
6

## just what is showing in the select

Just want to show the - isbn, total quantity, and the percentage of profit for the Top 5 highest total quantity. I looked in to the analytic func and not seeing how to do it?

thanks,
capriman

5. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776

6. Registered User
Join Date
Dec 2011
Posts
6

## still not getting answer for top 5

SELECT b.ISBN,sum(o.quantity) as TOTALQTYSold,avg((RETAIL-COST)/COST*100) AS PERcentPROFIT
FROM BOOKS b, orderitems o
WHERE b.isbn=o.isbn and rownum < 6
group by b.isbn
order by TOTALqtysold desC;

getting

only two rows selected not clear on how to modify above

ISBN TOTALQTYSOLD PERCENTPROFIT
---------- ---------------------- ----------------------
0401140733 5 54.92957746478873239436619718309859154925
1059831198 1 65.12

2 rows selected

thanks for helping
capriman

7. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
why not use analytics?

rownum for/from which table?

8. Registered User
Join Date
Mar 2007
Posts
627
Did you read the article anacedent posted the link? If so, here is another one: ORACLE-BASE - Top-N Queries
Congratulations for implementing the "What not to do!" method. I think its title is self-explanatory, but there are other correct methods described below that.

Exact query depends on the definition what "top 5 quantity" exactly means in case when some quantities are same. Shall it be only 5 rows even when there are 6 ISBNs with the same (greatest) quantity? Or do you want rows with 5 different (top) quantities?
Anyway, both cases are described in the article from the link above.

ISBN is unique in real world. If it is unique even in your data model (declared by unique or primary key constraint), just add "the rest of the columns" (only from BOOKS table) to the SELECT clause. In older Oracle versions, you will have to add them to GROUP BY clause, but, as they do not multiply resultset, that would not matter anyway.
Last edited by flyboy; 12-11-11 at 00:20. Reason: typos

9. Registered User
Join Date
Dec 2011
Posts
6

## not sure where to insert the select clause to handle rownum

SELECT b.ISBN,sum(o.quantity) as TOTALQTYSold,avg((RETAIL-COST)/COST*100) AS PERcentPROFIT
FROM BOOKS b, orderitems o
WHERE b.isbn=o.isbn
group by b.isbn
order by TOTALqtysold desC;

the above works- but need that top 5
I created the rownum_order_test table as the article recommends but not sure where to nest the below SELECT STATEMENT TO get the top five???

SELECT val
FROM (SELECT val
FROM rownum_order_test
ORDER BY val DESC)
WHERE ROWNUM <= 5;

thanks soo much again,
capriman

10. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
Code:
```WITH nested
AS (SELECT b.isbn,
SUM(o.quantity)                     AS totalqtysold,
Avg(( retail - cost ) / cost * 100) AS percentprofit
FROM   books b,
orderitems o
WHERE  b.isbn = o.isbn
GROUP  BY b.isbn
ORDER  BY totalqtysold DESC)
SELECT *
FROM   nested
WHERE  ROWNUM < 6;```

11. Registered User
Join Date
Dec 2011
Posts
6

## Thanks that worked

Thanks soo much - that makes sense - never used the Nested word??

Thanks again,
Capriman

12. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
when all else fails Read The Fine Manual

SELECT

13. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
"Nested" could have been "test", "abc", "xyz", whatever - it is just a subquery or inline view name. Subquery factoring is also known as a "WITH clause"; quite a useful feature.

14. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
Anacedent's with clause can be rewritten as

Code:
```SELECT *
FROM   (SELECT b.isbn,
SUM(o.quantity)                     AS totalqtysold,
Avg(( retail - cost ) / cost * 100) AS percentprofit
FROM   books b,
orderitems o
WHERE  b.isbn = o.isbn
GROUP  BY b.isbn
ORDER  BY totalqtysold DESC)
WHERE  ROWNUM < 6;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•