CODEWITHSUNDEEP
sqlsql-server
Saif Khan
Saif Khan

Reputation: 18772

SQL Query Advice - Get Previous Customer Order

I have the following table

custid    ordid   qty      datesold
1         A2       12      2008-01-05
2         A5       5       2008-01-02
1         A1       5       2008-01-01
2         A7       3       2007-02-05

What't the best way of getting the previous order for every customer?

Thanks

Upvotes: 1

Views: 1759

Answers (4)

Tom H
Tom H

Reputation: 47444

This is very similar to the other question that 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 rows with identical datesold values.

Upvotes: 0

Robert Wagner
Robert Wagner

Reputation: 17793

A common solution for this kind of problem is to choose the Max(datesold) and get the latest that way. This is ok if the custid/datesold combination is unique, but if there were two orders on the same day, it can cause duplicates.

If you have SQL 2005 or higher, you can use the Row_Number function to rank each customers orders 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 picks the same item, even if they have the same datesold, add some more items (such as RowID, recieptNumber) into the ORDER BY clause of the Row_number.

If you don't have SQL 2005, by adding identity column 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 of this is that there will be a table lookup at least for every customer if not every row.

If you are lucky you want to get the latest order processed, you could:

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

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

Assumption: Datesold will be in a ascending order (previous order's date will be lesser than current one)

Lets say you want to get the Order before A5 for customer 2. Here is how the query can be.


SELECT TOP 1 *
FROM Orders
WHERE DateSold < (SELECT DateSold FROM Orders WHERE CustId = 2 and OrdID = A5)
AND CustId = 2

Upvotes: 0

Tomalak
Tomalak

Reputation: 338118

If by "previous" you mean "the one before the latest":

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 datesold has to be a DATETIME with distinct enough values for this to work. A date alone will not be enough. If you have a record created date for example, this would be a good substitution for datesold.

Upvotes: 3

Related Questions