selecting the last 3 orders made

[noks]

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

 

[SQL_Hell]

sql server 2003? there is no such thing, you either mean 2000 or 2005.

How is your invoice number set up, is it incremental? or is there any created date on invoices?

 

[noks]

Sorry my mistake. it's 2005. Yes the invoice is incremental. I've tried using TOP predicate (SELECT Top 3...) but i only get 3 rows. It's the first time i'm using it so am not sure if my syntax is incorrect or what

CODE:

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

Thanks
Noks

 

[tehNellie]

SELECT TOP n will only give you n rows of data. If you can sort by orderdate or invoicenumber then that should give you your last 3 orders.

 

[SQL_Hell]

Yes your syntax looks fine

So is 3 rows not what you want then?

 

[noks]

I get what u're sayin, but i need d last 3 orders from each and every customer. if i hav 10 the results should be 10*3

thanks

 

[SQL_Hell]

hi again

basically you are going to need to group by customer or use a loop to pick up customer as a variable and select top 3 for each customer one by one.

Have a play with group by and see what you can come up with.

 

[SQL_Hell]

lol I keep reading the same posts from you on dbforums.com

 

[noks]

Hi SQL Hell, yeah i subscribed to both just to be on the safe side

I'll try yo advise
Thanks

 

[noks]

Group by doesn't seem to be making any difference. How do i use a loop in SQL server. Pls kindly advise, even if it's a syntax. (my first time using SQL server so am kinda lost)

Pls help

 

[pdx_man]

Is the order date a DATETIME field or SMALLDATETIME? Do you have the time of the order, too? I will assume you have.
You don't need a loop. It would look something like this:

SELECT mo.*
FROM MyOrders mo
INNER JOIN (Select TOP 3 mo2.CustID, mo2.OrdDate FROM MyOrders mo2 WHERE mo2.CustID = mo.CustID Order By mo2.OrdDate DESC) mo3
ON mo.CustID = mo3.CustID AND mrdDate = mo3.OrdDate

Haven't tested it, but this should give you the framework.

 

[noks]

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