Sql-server – Select everything besides the Max(date) for each group

selectsql server

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

Best Answer

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

Related Solutions

Sql-server – Query to get queue position for each group

This is a Gaps and Islands question. See here for more details on problems like this.

This should do what you need:

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions
;WITH cte_Companies
AS
(
    SELECT ID
           ,Company
           ,CASE 
              WHEN LAG(Company) OVER(ORDER BY ID) = Company  
              THEN 1
              ELSE 0
            END AS CompanyPosition
    FROM @Companies
)

SELECT ID, Company
FROM cte_Companies
WHERE CompanyPosition = 0

UPDATE Andriy noted that my solution was a SQL Servre 2012+ solution. The following code should work for versions down to 2005.

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
                -- Further test data
                ,(7, 'genco', 'London')
                ,(8, 'genco', 'Portsmouth')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions

SELECT ID, Company
FROM @Companies c1
WHERE NOT EXISTS    (
                        SELECT *
                        FROM @Companies c2
                        WHERE c1.Company = c2.Company
                        AND c1.ID - 1 = c2.ID
                    )

Select two record containing dates in it with two input dates

Simple answer:

If you want both date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
  AND 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
   OR 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the ranges that are "overlapping with the range of the input dates
(seems the most likely, based on the wanted result):

SELECT category 
FROM tablename 
WHERE 'start_date' <= end_date
  AND start_date <= 'end_date' ;

Best Answer

Related Solutions

Sql-server – Query to get queue position for each group

Select two record containing dates in it with two input dates

Related Question