Select everything besides the Max(date) for each group I have a table where I would like to pull everything besides the max dates for each groupID.
Is there an easy way to do it?
here is what i am trying to do:
SELECT 
groupId, 
Types,
MAX(Dates) as date

FROM TableData

Where Dates &lt; Max(dates)

GROUP BY 
groupId, 
Types

//The table looks as follows:
ID      |   GroupID | Date
1       |     A     | 10-10-2020 -&gt; don't show
2       |     A     | 09-10-2020
3       |     A     | 08-10-2020
4       |     B     | 10-10-2020 -&gt; 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
This is a classic situation where the use of window functions can come to the rescue!
Something like:
SELECT * FROM
(
  SELECT 
    group_id, 
    dates,
    ROW_NUMBER () OVER (PARTITION BY group_id
                        ORDER BY dates DESC) AS rn
  FROM table_data
) AS t
WHERE rn != 1
ORDER BY group_id, dates DESC;

   

Can I urge you not to use a keyword as an alias - use something like my_date or the_date instead - it helps readability and is less prone to problems when debugging!
Also, please include your SQL Server version in your questions.
Finally, you could make things a lot easier for those trying to help you if you provided a fiddle with your tables and data - this allows those answering to test their replies and helps eliminate duplication of effort (include the fiddle data in the question also).