jleachomg Posted October 1, 2013 Share Posted October 1, 2013 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 1, 2013 Share Posted October 1, 2013 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. Link to comment Share on other sites More sharing options...
jleachomg Posted October 3, 2013 Author Share Posted October 3, 2013 Ok- thanks for the new way of looking at it. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now