Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Unanswered: [Sql query]How to find the orderID and the maximum-price unit of each order

    Hi everyone! I have a question:
    How to select the orderID, order date and the maximum-price unit of each order in the below tables in 2 ways:

    1-using correlated sub-query
    2-not using correlated sub-query

    I only selected orderID, order date and the maximum price of each order but not the unit name.
    This is my query:

    Code:
    SELECT dbo.[Order Details].OrderID, Max(dbo.[Order Details].UnitPrice)as MaxUnitprice, dbo.Orders.OrderDate
    FROM  dbo.[Order Details] INNER JOIN
                   dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
                   dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID
    GROUP BY dbo.[Order Details].OrderID, dbo.Orders.OrderDate
    Please help me!
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have a look at this.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Another way ..

    WITH X (order_id, order_date, unit_price, unit_price_max)
    AS
    (SELECT D.order_id, O.order_date,
    D.unit_price, MAX(D.unit_price) OVER (PARTITION BY D.order_id)
    FROM Orders AS O,
    Order_Details AS D,
    Products AS P
    WHERE D.product_id = P.product_id
    D.order_id = O.order_id)

    SELECT X.*
    FROM X
    WHERE unit_price = unit_price_max;

    I have no idea if this better than row numbering

  4. #4
    Join Date
    Apr 2011
    Posts
    11
    @Win the example in that topic is a little bit different as the data is on the same table. it's like what I did above. Mine is from different table.

    @Celko It does not work. . I tried it but it showed nothing.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Umm, Mr. Celko's code works for me. Try using cut and paste to see if this works for you:
    Code:
    DECLARE @orders         TABLE (
       OrderID              INT         NOT NULL
    ,  OrderDate            DATE        NOT NULL
       )
    DECLARE @OrderDetails   TABLE (
       OrderID              INT         NOT NULL
    ,  ProductId            INT         NOT NULL
    ,  UnitPrice            MONEY       NOT NULL
       )
    DECLARE @Products       TABLE (
       ProductID            INT         NOT NULL
    ,  ProductName          NVARCHAR(9) NOT NULL
       )
    
    INSERT INTO @Products VALUES
       (1, N'One'), (2, N'Two'), (3, N'Three')
    ,  (4, N'Four'), (5, N'Five')
    
    INSERT INTO @Orders VALUES
       (11, '2013-01-11'), (22, '2013-01-22'), (33, '2013-02-02')
    ,  (44, '2013-02-13'), (55, '2012-02-24')
    
    INSERT INTO @OrderDetails VALUES
        (11, 1, 1)
    ,   (22, 1, 500), (22, 2, 222), (22, 4, 4)
    ,   (33, 5, 9), (33, 2, 2), (33, 3, 3)
    ,   (44, 1, 1), (44, 2, 1), (44, 3, 1)
    ,   (55, 1, 1), (55, 2, 2), (55, 4, 2)
    ;
    
    WITH c (order_id, order_date, unit_price, unit_price_max, product_name)
    AS (
        SELECT
       o.OrderID, o.OrderDate, d.UnitPrice
    ,  Max(d.UnitPrice) OVER (PARTITION BY d.OrderID)
    ,  p.ProductName
       FROM @orders AS o
       JOIN @OrderDetails AS d
          ON (d.OrderID = o.OrderID)
       JOIN @Products AS p
          ON (p.ProductID = d.ProductId)
    )  SELECT order_id, order_date, unit_price, unit_price_max, product_name
       FROM c
       WHERE  c.unit_price = c.unit_price_max
    If it does not work, please post the output.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2011
    Posts
    11
    Thank you, it's working, even though the query above has a few mistakes, but generally it's working.

    So, this is the case which is using the correlated sub-query, right.

    Would you might to suggest me another way to do that by not using the correlated sub-query.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by chaocanha View Post
    Would you might to suggest me another way to do that by not using the correlated sub-query.
    My suggestion uses a Window function (the OVER clause/operator). Try it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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