PostgreSQL SELECT the last order per customer per date range

Question

In PostgreSQL: I have a Table that has 3 columns:

CustomerNum, OrderNum, OrderDate.

There may(or may not) be many orders for each customer per date range. What I am needing is the last OrderNum for each Customer that lies in the date range that is supplied. What I have been doing is getting a ResultSet of the customers and querying each one separately, but this is taking too much time.

Is there any way of using a sub-select to select out the customers, then get the last OrderNum for each Customer?

Answer 1

SELECT t1.CustomerNum, t1.OrderNum As LastOrderNum, t1.LastOrderDate
  FROM table1 As t1
 WHERE t1.OrderDate = (SELECT MAX(t2.OrderDate)
                         FROM table1 t2
                        WHERE t1.CustomerNum = t2.CustomerNum
                          AND t2.OrderDate BETWEEN date1 AND date2)
   AND t1.OrderDate BETWEEN date1 AND date2

Answer 2

select customernum, max(ordernum)
from table
where orderdate between '...' and '...'
group by customernum

that's all.

Answer 3

Not sure about your Customer table's structure or relationships, but this should work:

SELECT Customer.Num, (
    SELECT OrderNum FROM Orders WHERE CustomerNum = Customer.Num AND OrderDate BETWEEN :start AND :end ORDER BY OrderNum DESC LIMIT 1
) AS LastOrderNum
FROM Customer

Answer 4

If by last order number you mean the largest order number then you can just use your select as the predicate for customer num, group the results and select the maximum:

SELECT CustomerNum, MAX(OrderNum) AS LastOrderNum
    FROM Orders
    WHERE 
        CustomerNum IN (SELECT CustomerNum FROM ...)
            AND
        OrderDate BETWEEN :first_date AND :last_date
    GROUP BY CustomerNum

If the last order number isn't necessarily the largest order number then you'll need to either find the largest order date for each customer and join it together with the rest of the orders to find the corresponding number(s):

SELECT O.CustomerNum, O.OrderNum AS LastOrderNum
    FROM
        (SELECT CustomerNum, MAX(OrderDate) AS OrderDate
             FROM Orders
             WHERE
                 OrderDate BETWEEN :first_date AND :last_date
                     AND
                 CustomerNum IN (SELECT CustomerNum FROM ...)
             GROUP BY CustomerNum
        ) AS CustLatest
            INNER JOIN
        Orders AS O USING (CustomerNum, OrderDate);