Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    Australia
    Posts
    7

    Unanswered: Nested query wont select max record.

    Hi guys,

    I am trying to select the max record from a nested query in db2. I have tested the nested query and it correctly lists the sums of records.

    When i try and find the MAX record of this nested query it spits out errors.

    <!-- max number of all products ordered --!>

    select po.prod_id, MAX(po.quantity_ord) "Max Products Ordered"
    from product_order po, order o
    where po.prod_id IN
    (select po.prod_id, SUM(po.quantity_ord)
    from product_order po, order o
    where po.order_id = o.order_id
    group by po.prod_id)
    ;

    Above is the sql that i have tried (among other variations)

    I think the problem lies in my where clause for the main query but im not sure.

    Any help is appreciated.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I may be wrong, but it appears that the nested query returns 2 values:

    po.prod_id,
    MAX(po.quantity_ord)

    but the IN clause of the main query says:

    where po.prod_id IN ...

    so po.prod_id is being compared to alternating values of prod_id and a number which is the SUM (I assume an integer value). But doesn't the first query really just expect a list of prod_id's for the IN list?

    Also, it appears that the first query is accessing 2 tables, but I don't see any join predicates like in the subquery (po.order_id = o.order_id).

    It might be better to use different table qualifiers for the main query and the subquery unless you specifically are trying to perform a correlated subquery.

    But I have to admit that I am not an expert in complex queries, nor I am quite sure what information you are trying to retrieve.

  3. #3
    Join Date
    May 2003
    Location
    Australia
    Posts
    7
    Thanks for replying.

    Well the nested part does return 2 values so how would i modify the query to only compare the po.prod_id of the nested part?

    Should i make another nested query to work out the SUM(po.quantity_ord) ?

    Im am trying to sum all the products for all the orders created and then display the product which has been ordered the most.

    This part of the query:

    (select po.prod_id, SUM(po.quantity_ord)
    from product_order po, order o
    where po.order_id = o.order_id
    group by po.prod_id)

    returns the product id and the sum of all the orders.

    I then want to get the product that has been ordered the most from the main query:

    select po.prod_id, MAX(po.quantity_ord) "Max Products Ordered"
    from product_order po, order o
    where po.prod_id IN

    but i cant extract the max record from the nested query.

    Basically it gets the first record from the nested querys result.

    Any tips ?

    I then want to convert it to a stored procedure so on a webpage people can click a button and it returns the result.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are probably many ways to do this but here is an idea that might work:

    Select 'Max Products Ordered', prod_id, sum(quantity_ord)
    from product_order
    group by 1, prod_id
    having sum(sales) =

    (select sum(quantity_ord)
    from product_order
    group by prod_id
    order by 1 desc
    fetch first row only)

    fetch first row only;

    Note that I did not access the order table. If you need some columns off that table (I didn't see you using any), you can join the two tables, but make sure you use join predicates in the where clause (for main query and sub-query).

    I tested this using the sales table in the SAMPLE database, but of course I substituted sales_person for prod_id when doing the test. I hope I translated the column and table names correctly back to your example.

    Note that "fetch first row only" is repeated at the bottom (for the main query) in case there are two products tied for the most sales and you only want one of them. If you leave this out, you will get mulitple rows of data in case of a tie for maximum sales.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you don't care about ties for first and only want one product, you can simplify even more:

    select 'Max Products Ordered', prod_id, sum(quantity_ord)
    from product_order
    group by prod_id
    order by 3 desc
    fetch first row only;

  6. #6
    Join Date
    May 2003
    Location
    Australia
    Posts
    7
    First question is, how do i fetch the first row for a query ?

    Your 2nd post would work if i was getting the max product ordered for a single order:

    select 'Max Products Ordered', prod_id, sum(quantity_ord)
    from product_order
    group by prod_id
    order by 3 desc
    fetch first row only;

    but i want to get max product ordered for ALL orders that have been placed, hence why i sum all of the products ordered and then grab the top one or top x if they are tied.

    I think the HAVING clause that you suggested help and i came up with the following query. Im at work so i cant test it until i get home tonight.

    If you give me your thoughts it would be appreciated

    nb: 'Max Products Ordered' is just renaming the sum(quantity_ord) column to something more readable. I can post up the ERD if it helps you picture what the DB is going to look like.

    select po.prod_id, "Max Products Ordered"
    from product order po
    group by "Max Products Ordered"
    having SUM(po.quantity_ord) "Max Products Ordered"
    (select SUM(quantity_ord)
    from product_order po, order o
    where po.order_id = o.order_id
    group by po.prod_id)
    order by SUM(quantity_ord) DESC

    although i dont think that would work since the nested quantity sum is always going to be larger than a single sum of a product.

    I somehow need to compare the prod_id of the summed products in the nested query.

    Thanks for all your help by the way.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With "fetch first row only" in a select statement, a query (or sub-query) that would normally retrieve multiple rows, will only return the first qualifying row. This is true for table rows or "group by" rows. You can sometimes manipulate which one of the rows is returned with the order by clause.

    I guess I don't understand your order file enough to help you. Based on most order files I have seen, my query would get the total for all orders, not just one order.

  8. #8
    Join Date
    May 2003
    Location
    Australia
    Posts
    7
    Im not after the total cost of an order.

    I am after the total number of times a product has been ordered for all orders.

    I am basing the query around how many times the product ID appears on an order and the quantity ordered for that product id.

    The whole idea is then to create a Stored procedure which the user inputs an integer value of say 10 which will then return the top 10 most ordered products.

    I hope this makes it a bit clearer, maybe you know a better way to implement this as a stored procedure?

    The SQL i have come up with to do this is:

    SELECT DISTINCT PO.PROD_ID, P.PROD_NAME, SUM(PO.QUANTITY_ORD) "Max Products Ordered"
    FROM PRODUCT_ORDER PO, PRODUCT P, ORDER O
    WHERE PO.ORDER_ID = O.ORDER_ID
    AND P.PROD_ID = PO.PROD_ID
    GROUP BY PO.PROD_ID, P.PROD_NAME
    ORDER BY SUM(PO.QUANTITY_ORD) DESC
    ;

    This should add all of the products ordered and the amount of times it has been ordered.

    The part i havent figured out yet is how to make the Stored procedure to loop and display the top 10 or top x amount of products.

    Cheers.
    Last edited by Krim; 05-19-03 at 12:57.

Posting Permissions

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