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

>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?

read up on how to use analytic functions.

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

How does one select the TOP N rows from a table? | Oracle FAQ

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

why not use analytics?

rownum for/from which table?

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.

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

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

Thanks again,
Capriman

when all else fails Read The Fine Manual

SELECT

"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.

Anacedent's with clause can be rewritten as