SQL Select everything besides the most recent date

Question

I have a table where I would like to pull all the dates in it besides the most recent one. I tried the following but it's giving me an error " an aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list"

This is what I tried:

SELECT 
groupId, 
Types,
MAX(Dates) as date

FROM TableData

Where Dates < Max(dates)

GROUP BY 
groupId, 
Types

//The table looks as follows:
ID      |   GroupID | Date
1       |     A     | 10-10-2020 -> don't show
2       |     A     | 09-10-2020
3       |     A     | 08-10-2020
4       |     B     | 10-10-2020 -> don't show
5       |     B     | 09-10-2020
6       |     B     | 08-10-2020


//Expected result:
GroupID | Date
  A     | 09-10-2020
  A     | 08-10-2020
  B     | 09-10-2020
  B     | 08-10-2020

Answer 1

If you want all the rows in the table expect for the one(s) whose date matches the latest date in the table, one option uses a subquery:

select t.*
from tabledata t
where date < (select max(date) from tabledata)

You can also express this with window functions (but it will not necessarily perform better):

select *
from (select t.*, max(date) over() max_date from tabledata t) t
where date < max_date

---

Edit: if you want that logic on a per-id basis, then we need to correlate the subquery:

select t.*
from tabledata t
where date < (select max(t1.date) from tabledata t1 where t1.id = t.id)

... or to use a partition by clause in the window max():

select *
from (select t.*, max(date) over(partition by id) max_date from tabledata t) t
where date < max_date