SQL Query - Retrieve Previous Sales Order
If by "previous" you mean "the one that was before the last":
SELECT TOP 1
ordid
FROM
orders
WHERE
custid = @custid
and datesold < (SELECT MAX(datesold) FROM orders i where i.custid = orders.custid)
ORDER BY
datesold DESC
Of course, there datesold
must be a DATETIME with certain sufficient values for this. Date alone is not enough. For example, if you have a post creation date, that would be a good substitute for datesold
.
source to share
A common solution for this kind of task is to select Max (dateold) and get the latest version. It's ok if the custid / dateold combination is unique, but if there were two orders on the same day, it could lead to duplicates.
If you have SQL 2005 or higher, you can use the Row_Number function to rank each customer order and select the first one for each:
SELECT custid, ordid, qty, datesold
FROM (
SELECT *,
Row_Number() OVER (PARTITION BY custid ORDER BY datesold desc) as 'Rank'
FROM tbl
)
WHERE Rank = 1
To make sure it always selects the same item, even if they have the same ending, add a few more items (for example, RowID, recieptNumber) to the ORDER BY clause of the Row_number.
If you don't have SQL 2005, by adding an identity column you can do similar things:
SELECT custid, ordid, qty, datesold
FROM tbl
WHERE id =
(SELECT TOP 1 id FROM tbl a WHERE custid = a.custID ORDER BY dateSold)
The downside to this is that there will be a table lookup for at least every customer, if not every row.
If you are lucky and want to receive the last processed order, you can:
SELECT custid, ordid, qty, datesold
FROM tbl
INNER JOIN (
SELECT a.id FROM tbl a GROUP BY tbl.custId
) s ON tbl.id = s.id
source to share
Assumption: Datesold will be in ascending order (the date of the previous order will be less than the current one)
Let's say you want to get an order up to A5 for customer 2. This is how a request might be.
SELECT TOP 1 *
FROM Orders
WHERE DateSold < (SELECT DateSold FROM Orders WHERE CustId = 2 and OrdID = A5)
AND CustId = 2
source to share
This is very similar to the other question you asked.
SELECT
T1.ordid
FROM
dbo.Sales T1
INNER JOIN dbo.Sales T2 ON
T2.custid = T1.custid AND
T2.datesold > T1.datesold
LEFT OUTER JOIN dbo.Sales T3 ON
T3.custid = T1.custid AND
T3.datesold > T1.datesold AND
T3.datesold < T2.datesold
WHERE
T1.custid = @custid AND
T3.custid IS NULL
Same caveat about strings with the same date values.
source to share