Need some help on using a variable

[jleachomg]

I have a nifty query that pulls data by orders.orderid > '85000' (and I update that number every week based on orders.) But I want it to automatically pull the most recent 3,000 orders instead of changing that number all the time, so I was hoping to set a variable which is Max(order.orderid)-3000, thus pulling the last 3000 order records. Here is my static select:

 

SELECT Orders.OrderID, Orders.OrderStatus, Orders.CustomerID, Orders.ShippingMethodID, Orders.BillingFirstName, Orders.BillingLastName, Orders.BillingAddress1, Orders.BillingAddress2, Orders.BillingCity, Orders.BillingState, Orders.BillingPostalCode, Orders.PaymentAmount, Orders.OrderDate, Orders.ShipDate, Orders.LastModified, fraud.score, CAST(Orders.OrderID AS VARCHAR) AS 'za_orderid_full', CAST(Orders.CustomerID AS VARCHAR) AS 'za_custid_full'FROM Orders JOIN Fraud ON Fraud.OrderID = Orders.OrderIDWHERE Orders.ShipDate IS NULL AND Orders.OrderID > '85000' AND Orders.OrderStatus <> 'Cancelled'

 

Can someone tell me how to declare and set that variable? Or suggest another way to accomplish this? Your help is greatly appreciated.

[justsomeguy]

Use LIMIT to get only the first 3000 records. You'll need to order by the orderID column in descending order, and then if you get the first 3000 records then that should do it. How to format the LIMIT clause will depend on which database system you're using, but you should be able to look that up.

[jleachomg]

Ok- thanks for the new way of looking at it.