Find orders in the last 12 months, max date being the last order date-CodePudding

I feel like this question has been asked before, I couldn't find the exact requirement or the database engine (PostgreSQL), so please feel free to link me to the original.

I have a seemingly simple requirement: given an orders table, I need to find orders in a 12-month range, but the ending date will be the max date found in the table. The column of interest is called order_date and stores dates in the YYYY-mm-dd format.

So, here's my attempt, but it fails, saying that the aliased column (max_order_date) doesn't exist:

SELECT * FROM orders WHERE order_date BETWEEN
    (SELECT max(order_date) FROM orders as max_order_date)
        AND (SELECT max_order_date - INTERVAL '1 year');

After seeing Postgres complain, I tried to write the query without an alias:

SELECT * FROM orders WHERE order_date BETWEEN
    (SELECT max(order_date) FROM orders as max_order_date)
        AND (SELECT max(order_date) - INTERVAL '1 year');

Only this time, I got this error: aggregate functions are not allowed in the WHERE clause.

I obviously am looking for the right way to do this query, but am also very eager to learn why the above two limitations exist. That is, why can't the aliasing work in the first case, and why aggregates aren't allowed in WHERE? To me it doesn't look like I'm asking for something twisted or impossible to compute.

Thank you!

CodePudding user response：

You can use the analytical function of max() over() to determine the max order date and then use a filter to check if the order date lies between 1 year and max order date.

select * 
  from (SELECT o.*
              ,max(order_date) over(partition by 1) as max_order_date
         FROM orders o 
       )x
where order_date>= x.max_order_date - INTERVAL '1 year'

CodePudding user response：

This is probably more efficient using a subquery:

select o.*
from orders o
where o.order_date >= (select max(order_date) - interval '1 year' from orders);

Both the subquery and the outer query can use an index on orders(order_date).