Results 1 to 6 of 6

Thread: Query Problem

  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: Query Problem

    I am stuck with a query. May be I am missing something.Plz have a look...

    Code:
    CREATE TABLE [Order](
    Orderid VARCHAR(10),
    orderdate DATETIME)
    
    CREATE TABLE Product(
    Prodid VARCHAR(10),
    ProdDes VARCHAR(20),
    ProdPrice INT)
    
    CREATE TABLE OrdDetails(
    OrddetailsID VARCHAR(10),
    OrdID VARCHAR(10),
    ProdID VARCHAR(10))
    
    
    INSERT INTO [Order] VALUES ('1','5/11/2006')
    INSERT INTO [Order] VALUES ('2','5/11/2006')
    INSERT INTO [Order] VALUES ('3','6/11/2006')
    
    INSERT INTO Product VALUES ('1','TV',16)
    INSERT INTO Product VALUES ('2','LCD',20)
    INSERT INTO Product VALUES ('3','DVD',9)
    INSERT INTO Product VALUES ('4','MP',19)
    
    
    INSERT INTO OrdDetails VALUES ('1','1','1')
    INSERT INTO OrdDetails VALUES ('2','1','2')
    INSERT INTO OrdDetails VALUES ('3','1','3')
    INSERT INTO OrdDetails VALUES ('4','2','2')
    INSERT INTO OrdDetails VALUES ('5','2','4')
    INSERT INTO OrdDetails VALUES ('6','3','2')
    
    ---Query
    
    select dd.orderdate,
    
            max(dd.totprice)
    
    FROM
    
    
    (select    TOP 100 PERCENT dbo.[Order].orderdate,
     dbo.[Order].Orderid,
     SUM(dbo. Product .ProdPrice) AS TotPrice
    FROM          dbo.OrdDetails INNER JOIN
                                                  dbo.[Order] ON dbo.OrdDetails.OrdID = dbo.[Order].Orderid INNER JOIN
                                                  dbo. Product ON dbo.OrdDetails.ProdID = dbo. Product .Prodid
                           GROUP BY dbo.[Order].orderdate, dbo.[Order].Orderid
                           ORDER BY dbo.[Order].orderdate, dbo.[Order].Orderid)dd 
    group by dd.orderdate
    
    
    
    ----Query
    Drop table [Order]
    Drop Table Product
    Drop Table OrdDetails

    The output needed is maximum summation of the order no ,I mean group by orderdate then orderno

    Orderdate Ordernumber
    5 nov 1
    6 nov 3

    at this momnet I am getting the orderdate and price
    Plz help
    Thanks!!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "maximum summation of the order no" ?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    "maximum summation of the order no" ?????
    Thanks Rudy,
    The requirement is -
    Date Orderid Sum(price)

    5/11/2006 1 41
    5/11/2006 2 39
    6/11/2006 3 20


    Now in each date we need to pick the highest sum(price) with respective to orderid
    So the final ouptput should be
    Orderdate Orderid
    5/11/2006 1
    6/11/2006 3
    I think now its a bit more clear...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select O.orderdate
         , O.Orderid
         , sum(P.ProdPrice) AS TotPrice
      from dbo.[Order] as O
    inner
      join dbo.OrdDetails as OD
        on OD.OrdID = O.Orderid
    inner
      join dbo.Product as P
        on P.Prodid = OD.ProdID
    group
        by O.orderdate
         , O.Orderid
    having sum(P.ProdPrice) =
           ( select max(TP)
               from (
                    select sum(dbo.Product.ProdPrice) AS TP
                      from dbo.[Order]
                    inner
                      join dbo.OrdDetails
                        on dbo.OrdDetails.OrdID 
                         = dbo.[Order].Orderid
                    inner
                      join dbo.Product
                        on dbo.Product.Prodid 
                         = dbo.OrdDetails.ProdID
                     where dbo.[Order].orderdate 
                         = O.orderdate
                    group
                        by dbo.[Order].Orderid
                    ) as same_day_orders
          )
    order
        by O.orderdate
    results:

    2006-05-11 00:00:00 1 45
    2006-06-11 00:00:00 3 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So what you really want is to find the orderid and orderdate for the order with the largest total price for a given day? If that is the case, then the best way that I know to get that answer is in stages, something like:
    Code:
    CREATE TABLE #ordersByDate (
       orderdate		DATETIME
    ,  orderid		INT
    ,  TotPrice		INT
       )
    
    INSERT INTO #ordersByDate (
       orderdate, orderid, TotPrice
       ) SELECT Convert(DATETIME, Convert(CHAR(10), o.orderdate, 121)) AS orderdate
    ,     o.orderid
    ,     Sum(p.ProdPrice) AS TotPrice
          FROM dbo.[Order] AS o
          INNER JOiN dbo.ordDetails AS od
             ON (od.ordID = o.orderid)
          INNER JOIN dbo.Product AS p
             ON (p.prodId = od.ProdId)
          GROUP BY o.orderdate, o.orderid
    
    
    SELECT orderdate, orderid
       FROM #ordersByDate AS obd
       WHERE obd.TotPrice = (SELECT Max(z1.TotPrice)
          FROM #ordersByDate AS z1
          WHERE  z1.orderdate = obd.orderdate)
       ORDER BY obd.orderdate, obd.orderid
    
    DROP TABLE #ordersByDate
    The problem is that this kind of question "pokes at the seams" of the SQL Engine. Because this kind of query requires careful sequencing of query evaluation in order to determine what occurs where/when, and SQL (like most query languages) doesn't offer explicit control of sub-expression evaluation. That's actually a blessing, because you get really complicated really fast when you try to handle things like that in a language, and simply making sequential steps is easy to write, read, and understand.

    I build a temp table, and populate it very similar to your derived table dd. Once I've populated that, I make a two stage pass through it to compare this row with the largest order for this row's date, and only return this row in the result set if it is the largest. Just a word of warning, this code does NOT eliminate ties, so it is possible to get multiple rows returned for a given date.

    R937's solution is elegant, and it is a single SQL operation, but for large result sets I think it will be rather slow because of the work that it needs to do to generate every row.

    -PatP

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thank you Great Guys!!
    Thanks for those wonderful solutions...
    Wishing you both A very HAPPY NEW YEAR 2007!!
    And wish you all a wonderful and prosperous New Year 2007.
    Enjoy !!!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

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