PostgreSQL CHOOSE the last order for each user in the date range

Question

In PostgreSQL: I have a table with three columns:

CustomerNum, OrderNum, OrderDate.

There may be (or may not) be many orders for each customer for a date range. I need the latest OrderNum for each Customer that falls within the date range that is delivered. What I did was getting ResultSet clients and requesting each separately, but it takes too much time.

Is there a way to use sub-selection to select customers, and then get the latest OrderNum for each customer?

Answer 1

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

what all.

Answer 2

postgres DISTINCT ON:

SELECT DISTINCT ON (CustomerNum) CustomerNum, OrderNum, OrderDate
  FROM Orders
  WHERE OrderDate BETWEEN 'yesterday' AND 'today'
  ORDER BY CustomerNum, OrderDate DESC;

. http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

Answer 3

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 4

, :

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 5

, , :

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

, , , ():

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);

Answer 6

-- generate some data
DROP TABLE tmp.orders;
CREATE TABLE tmp.orders
    ( id INTEGER NOT NULL
    , odate DATE NOT NULL
    , payload VARCHAR
    )
    ;
ALTER TABLE tmp.orders ADD PRIMARY KEY (id,odate);

INSERT INTO tmp.orders(id,odate,payload) VALUES
  (1, '2011-10-04' , 'one' )
, (1, '2011-10-24' , 'two' )
, (1, '2011-10-25' , 'three' )
, (1, '2011-10-26' , 'four' )
, (2, '2011-10-23' , 'five' )
, (2, '2011-10-24' , 'six' )
    ;

-- CTE to the rescue ...
WITH sel AS (
    SELECT * FROM tmp.orders
    WHERE odate BETWEEN '2011-10-23' AND '2011-10-24'
    )
SELECT * FROM sel s0
WHERE NOT EXISTS (
    SELECT * FROM sel sx
    WHERE sx.id = s0.id
    AND sx.odate > s0.odate
    )
    ;

:

DROP TABLE
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
ALTER TABLE
INSERT 0 6
 id |   odate    | payload 
----+------------+---------
  1 | 2011-10-24 | two
  2 | 2011-10-24 | six
(2 rows)