Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: Oracle9i/Group Functions: MAX()

    hi guys,
    here is the question in my assignment:
    List the most expensive book purchased by customer 1017?
    i have these tables: books, customers, orders, orderitems.
    my answer was:
    SELECT MAX(retail) " Most Expensive Book"
    FROM orders NATURAL JOIN orderitems NATURAL JOIN books
    where customer#=1017
    ;
    I get the right answer but i can't get both the book title and its price at the same time on the output. i only get this :

    Most Expensive Book
    --------------------
    89.95

    So, is there any other way to write the SQL code and get something like this: |
    Title
    ------------------------
    Handcranked Computeres

    Most Expensive Book
    -------------------
    89.95

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    not knowing all the columns it is hard to advise you specifically
    why not also select the book_name.

    once you select the book_name you will have to add one more line
    due to your aggregate function.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your question is somehow ambiguous, what if the same customer has ordered two books with the same maximum price, which one would you get ?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Show the table structures and by the way, you can use NATURAL JOIN for an adhoc query, but don't EVER use it for production code. Eventually your application will break when a new column is added to one of the tables. Always specify the join columns, a NATURAL JOIN is just a lazy way to do less typing and has no place in real code. IMHO
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Oct 2004
    Posts
    9
    thanks guys for your reply , but could you please explain more to me a;; these?
    here are the tables i'm working with:
    Customer(customer#,firstname,lastname,address,refe rred)
    books(isbn,title,pubdate,pubid,cost,retail,categor y)
    orders(order#,customer#,orderdate,shipdate,shipadd ress)
    orderitems(order#,item#,isbn,quantity)

    and the question is:
    List the most expensive book purchased by customer#1017.

    my answer was:
    SELECT MAX(retail) " Most Expensive Book"
    FROM orders NATURAL JOIN orderitems NATURAL JOIN books
    where customer#=1017
    ;

    ---> this code answers the question BUT it only gives me the price of the most expensive book (only one column). i need to show both the price and the title of the book. Any suggestions?

    s/o told me to use a derived table? what is that?
    also, the book_name function! how does it work?

  6. #6
    Join Date
    Sep 2004
    Posts
    16
    You could use subqueries:

    select title, retail from books
    where retail = ( select max(retail)
    from books
    where isbn in (
    select isbn from orderitems
    where order# in
    (select order#
    from orders
    where customer# = 1017
    )
    )
    )

    This would also take care of the case where you have 2 books purchased by customer 1017, which have the same max retail price

    --Vinita
    Last edited by vinitasinha; 11-04-04 at 03:41.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    vinitasinha, that will still list the set of books..
    Code:
    SQL@8i> create table books
      2   as select 1000 + trunc( dbms_random.value * 100000 ) isbn,
      3             'Book ' || rownum title,
      4             trunc( sysdate,'YY' ) + trunc( dbms_random.value * 365 ) pubdate,
      5             1000 + trunc( dbms_random.value * 1000 ) pubid,
      6             round( dbms_random.value * 50,2 ) cost,
      7             round( dbms_random.value * 50,2 ) retail,
      8             chr( 65 + trunc( dbms_random.value * 6 ) ) category
      9        from all_objects
     10       where rownum <= 20
     11  /
    
    Table created.
    
    SQL@8i> select * from books;
    
          ISBN TITLE                                         PUBDATE        PUBID       COST     RETAIL CA
    ---------- --------------------------------------------- --------- ---------- ---------- ---------- --
         42638 Book 1                                        10-DEC-04       1138      21.29       46.7 D
         93255 Book 2                                        09-SEP-04       1754      37.31      15.58 B
         69652 Book 3                                        14-JUN-04       1102      32.61      49.32 F
         95560 Book 4                                        14-NOV-04       1333      46.41      24.75 F
         90211 Book 5                                        28-DEC-04       1871      42.62      49.06 C
         20283 Book 6                                        11-FEB-04       1802      19.78      20.06 B
         52456 Book 7                                        24-DEC-04       1700      18.11      38.59 A
         99590 Book 8                                        21-NOV-04       1887      24.62      37.71 B
         17956 Book 9                                        12-JUN-04       1151      23.86      21.86 D
         71371 Book 10                                       22-APR-04       1937        .51      13.91 F
         93609 Book 11                                       12-FEB-04       1806      27.85       7.09 E
         45292 Book 12                                       14-MAR-04       1433      40.12        .72 E
         11890 Book 13                                       06-DEC-04       1766      11.04      21.03 B
         20205 Book 14                                       23-JAN-04       1442       1.17      16.49 C
         97893 Book 15                                       23-NOV-04       1422         37      20.29 E
         15103 Book 16                                       09-JUN-04       1096      25.35      45.96 F
         88022 Book 17                                       05-JUN-04       1093      16.08      14.75 E
          5278 Book 18                                       11-APR-04       1379      45.91      34.91 F
         85966 Book 19                                       22-NOV-04       1370         19       1.68 D
         34352 Book 20                                       22-NOV-04       1011      11.48      14.85 D
    
    20 rows selected.
    
    SQL@8i> insert into books values ( 12345, 'Book 21', trunc( sysdate ), 1489, 45.25, 14.85, 'A' );
    
    1 row created.
    
    SQL@8i> select * from books;
    
          ISBN TITLE                                         PUBDATE        PUBID       COST     RETAIL CA
    ---------- --------------------------------------------- --------- ---------- ---------- ---------- --
         42638 Book 1                                        10-DEC-04       1138      21.29       46.7 D
         93255 Book 2                                        09-SEP-04       1754      37.31      15.58 B
         69652 Book 3                                        14-JUN-04       1102      32.61      49.32 F
         95560 Book 4                                        14-NOV-04       1333      46.41      24.75 F
         90211 Book 5                                        28-DEC-04       1871      42.62      49.06 C
         20283 Book 6                                        11-FEB-04       1802      19.78      20.06 B
         52456 Book 7                                        24-DEC-04       1700      18.11      38.59 A
         99590 Book 8                                        21-NOV-04       1887      24.62      37.71 B
         17956 Book 9                                        12-JUN-04       1151      23.86      21.86 D
         71371 Book 10                                       22-APR-04       1937        .51      13.91 F
         93609 Book 11                                       12-FEB-04       1806      27.85       7.09 E
         45292 Book 12                                       14-MAR-04       1433      40.12        .72 E
         11890 Book 13                                       06-DEC-04       1766      11.04      21.03 B
         20205 Book 14                                       23-JAN-04       1442       1.17      16.49 C
         97893 Book 15                                       23-NOV-04       1422         37      20.29 E
         15103 Book 16                                       09-JUN-04       1096      25.35      45.96 F
         88022 Book 17                                       05-JUN-04       1093      16.08      14.75 E
          5278 Book 18                                       11-APR-04       1379      45.91      34.91 F
         85966 Book 19                                       22-NOV-04       1370         19       1.68 D
         34352 Book 20                                       22-NOV-04       1011      11.48      14.85 D
         12345 Book 21                                       04-NOV-04       1489      45.25      14.85 A
    
    21 rows selected.
    
    SQL@8i> create table orders as
      2  select rownum order#, 1017 customer#, trunc( sysdate ) - ( 3 + trunc( dbms_random.value * 10 ) ) orderdate,
      3         trunc( sysdate ) - trunc( dbms_random.value * 3 ) shipdate, 'Customer 1017 Address' shipaddress
      4    from all_objects
      5   where rownum <= 2
      6  /
    
    Table created.
    
    SQL@8i> select * from orders;
    
        ORDER#  CUSTOMER# ORDERDATE SHIPDATE  SHIPADDRESS
    ---------- ---------- --------- --------- ---------------------
             1       1017 29-OCT-04 02-NOV-04 Customer 1017 Address
             2       1017 27-OCT-04 01-NOV-04 Customer 1017 Address
    
    SQL@8i> create table orderitems as select rownum order#, rownum item#, decode( rownum, 1, 34352, 12345 ) isbn,
      2                                       1 quantity
      3                                  from all_objects
      4                                 where rownum <= 2;
    
    Table created.
    
    SQL@8i> select * from orderitems;
    
        ORDER#      ITEM#       ISBN   QUANTITY
    ---------- ---------- ---------- ----------
             1          1      34352          1
             2          2      12345          1
    
    SQL@8i> select title, retail from books
      2  where retail = ( select max(retail)
      3  from books
      4  where isbn in (
      5  select isbn from orderitems
      6  where order# in
      7  (select order#
      8  from orders
      9  where customer# = 1017
     10  )
     11  )
     12  )
     13  /
    
    TITLE                                             RETAIL
    --------------------------------------------- ----------
    Book 20                                            14.85
    Book 21                                            14.85
    
    SQL@8i>
    .. same as ( but will better plan ) ..
    Code:
    SQL@8i> select b.title, max(b.retail)
      2    from orders o, orderitems s, books b
      3   where o.order# = s.order#
      4     and s.isbn = b.isbn
      5     and o.customer# = 1017
      6   group by b.title
      7  /
    
    TITLE                                         MAX(B.RETAIL)
    --------------------------------------------- -------------
    Book 20                                               14.85
    Book 21                                               14.85
    
    SQL@8i>
    Because of this, he must decide: One book ( and then define how he will select this book ) or The set of books ?

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    this will show you all books that EQUAL the most_expensive price
    Could be one book, but could be many depending on how many ordered
    had the same price matching the highest. Add a distinct to the first line to
    remove the duplicates, if any.
    PHP Code:
    select customer#, title, retail 
    from (select c.customer#,
                 
    b.title,
                 
    b.retail,
                 
    max(b.retailover (partition by customer#) most_expensive
          
    from 
             customer c
    ,
             
    orders o,
             
    orderitems oi,
             
    books b
          where 
             c
    .customer# = 1017        and
             
    c.customer# = o.customer# and
             
    o.order#    = oi.order#   and
             
    oi.isbn     b.isbn )
    where retail most_expensive
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Oct 2004
    Posts
    9
    thanks everyone for your contribution i got it now
    i ended up doing this:

    select title, retail "Most Expensive Book"
    from books
    where retail IN(select max(retail)
    from books natural join orderitems natural join orders
    where customer#= 1017)
    ;
    TITLE
    ------
    PAINLESS CHILD-REARING

    Most Expensive Book
    --------------------
    89.95

    SWEET!!

  10. #10
    Join Date
    Oct 2004
    Posts
    9
    thanks everyone for your contribution i got it now
    i ended up doing this:

    select title, retail "Most Expensive Book"
    from books
    where retail IN(select max(retail)
    from books natural join orderitems natural join orders
    where customer#= 1017)
    ;
    TITLE
    ------
    PAINLESS CHILD-REARING

    Most Expensive Book
    --------------------
    89.95

    SWEET!!

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    I might buy that book!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Aug 2004
    Posts
    330
    Of course, you will find it in the "fiction" section.

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    hahah!
    good one.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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