Results 1 to 14 of 14
  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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 13:33.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    read up on how to use analytic functions.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    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. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    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. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why not use analytics?

    rownum for/from which table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    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 01:20. Reason: typos

  9. #9
    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. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Dec 2011
    Posts
    6

    Thanks that worked

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

    Thanks again,
    Capriman

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

    SELECT
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "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. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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