Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2007
    Posts
    91

    Unanswered: selecting the last 3 orders made

    Hi guys

    Am new in SQL server. i use SQL server 2003 ans am trying to retieve the last three orders made. Can you please help me with my Select statement so that it will only select the last three coz @ the moment it selects everything

    CODE:
    SELECT dbo.Invoice.InvoiceNo, dbo.Sales.UnitsSold, dbo.Sales.QuantitySold, dbo.Sales.CostAmount, dbo.Item.ItemCode, dbo.Item.ItemDescription,
    dbo.Item.Item, dbo.PurchaseOrder.OrderNumber, dbo.PurchaseOrder.OrderDate, dbo.PurchaseOrder.OrderStatus, dbo.PurchaseOrder.QuantityOrdered,
    dbo.PurchaseOrder.QuantityReceived, dbo.Customer.CustomerCode
    FROM dbo.Invoice INNER JOIN
    dbo.Sales ON dbo.Invoice.InvoiceKey = dbo.Sales.InvoiceKey INNER JOIN
    dbo.Item ON dbo.Sales.ItemKey = dbo.Item.ItemKey INNER JOIN
    dbo.PurchaseOrder ON dbo.Sales.VendorKey = dbo.PurchaseOrder.VendorKey INNER JOIN
    dbo.Customer ON dbo.Sales.CustomerKey = dbo.Customer.CustomerKey

    Thanks
    Noks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need to use the TOP predicate (SELECT TOP 3...)
    and you need to ORDER BY whatever field determines when the order sold
    (ShipDate?) in DESC order.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2007
    Posts
    91
    Thanks Red, i've tried dat but now i only get 3 records wheres i want the last 3 for all the customers. Maybe my code is wrong. Pls help

    CODE:

    SELECT TOP (3) OrderDate, OrderNumber, OrderStatus, QuantityOrdered, QuantityReceived
    FROM dbo.PurchaseOrder
    ORDER BY OrderDate

    Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Have a look at this thread:
    http://www.dbforums.com/showthread.php?t=1617547
    It's going in the same direction. georgev's solution should do it.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Mar 2007
    Posts
    91
    Thanx Red. I think i wasn't clear enough in... i want the last 3 orders to b for each and every customer ie if i have 10 customers i wanna get 10*3 records which will mean 3 for each customer

    Thanks

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I understood. That's basically the same thing the person is asking for in the other thread.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Mar 2007
    Posts
    91
    i finally got it. I needed 2 use the query windows and not the query design
    CODE:


    SELECT * FROM
    (
    SELECT C.Customercode, O.OrderDate, O.OrderNumber, ROW_NUMBER()
    OVER (
    PARTITION BY C.CustomerCode ORDER BY O.OrderDate DESC
    ) AS RecID
    from Customer C Left Outer Join Sales K
    on K.Customerkey = C.Customerkey
    Left Outer Join purchaseorder O
    on O.vendorkey = K.vendorkey
    ) AS d
    WHERE RecID BETWEEN 1 AND 3

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by noks
    i finally got it.
    Well done
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83381

    Please can you let us know if you post the question on other forums too? This saves people covering ground that has been covered by others.

    Thank you for letting us know it was resolved though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2007
    Posts
    91
    pootle flump to answer yo Q. I joined 2 other forums and whenever i get a solution i make sure that i post the answer in ol forums & specify that the problem has been resolved

    PS: When i don't say so in any of my posts that means i'm still looking for the answer

    noks

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi noks

    Indeed - and I appreciate that you have let us know the solution which most don't.

    However, there were lines of enquiry at sqlteam that would have helped people here. I would appreciate it if you post links to any duplicated posts so that the same ground is not covered by other members.

    In any event I typically point out any cross posts btw sqlteam and here - I missed this one at the time. However, it usually goes down better if the OP lets us know than if a memeber notices and points it out.

    Thanks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2007
    Posts
    91
    K, get u. I'll do so next time

    Noks

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    For selecting between limits (like MySQL's LIMIT clause) use a TOP of a TOP
    e.g.
    Code:
    SELECT TOP(3) * FROM (
      SELECT TOP(20) column1,column2,column3
      FROM [table]
      ORDER BY column1 DESC
    )
    ORDER BY column1 ASC
    The above code will give you the records from 17->20. Note the use of DESC and ASC to rearrange your results to give you what you're after.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    thankyou noks

    aschk - you can't use order by in a derived table in SS2005
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Silly SS2005

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aschk
    Silly SS2005
    Lol. Be careful - set based purists would disagree.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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