CODEWITHSUNDEEP
sql-servert-sqlgroup-by
Kerberos
Kerberos

Reputation: 331

Select ID for corresponding max date using GROUP BY

My table structure as below

Category    Sex         Last Modified Date          Id
7           2           2015-01-16                  87603
7           1           2014-11-27                  87729
7           2           2018-09-06                  87135
7           1           2017-12-27                  87568

My sql query as below

SELECT
    MAX(Id) AS Id
FROM 
    Table
GROUP BY 
    Category, Sex

Result as below

87603
87729

But I would like to get Id as Max Last Modified Date. Correct result should be as below

87135
87568

Upvotes: 0

Views: 1699

Answers (5)

Salman Arshad
Salman Arshad

Reputation: 272386

You can use ROW_NUMBER() to find most recent row per group:

SELECT Id, LastModifiedDate
FROM (
    SELECT Id, LastModifiedDate, ROW_NUMBER() OVER (PARTITION BY Category, Sex ORDER BY LastModifiedDate DESC) AS rnk
    FROM t
) AS cte
WHERE rnk = 1

Use RANK() if you're interested in finding all rows with ties for LastModifiedDate.

Upvotes: 2

Jason A. Long
Jason A. Long

Reputation: 4442

Here are a few different approaches... (in no particular order)

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;
GO

CREATE TABLE #TestData (
    Category TINYINT NOT NULL,
    Sex TINYINT NOT NULL,
    LastModifiedDate DATE NOT NULL,
    Id INT NOT NULL 
    );
GO

INSERT #TestData(Category, Sex, LastModifiedDate, Id) VALUES 
    (7, 2, '2015-01-16', 87603),
    (7, 1, '2014-11-27', 87729),
    (7, 2, '2018-09-06', 87135),
    (7, 1, '2017-12-27', 87568);
GO 

/* nonclustered index to support the query. */
CREATE UNIQUE NONCLUSTERED INDEX ix_TestData_Category_Sex_LastModifiedDate
    ON #TestData (Category ASC, Sex ASC, LastModifiedDate DESC)
    INCLUDE (Id);
GO 

--====================================================

-- option 1: TOP(n) WITH TIES...
SELECT TOP (1) WITH TIES
    td.Id
FROM
    #TestData td
ORDER BY 
    ROW_NUMBER() OVER (PARTITION BY td.Category, td.Sex ORDER BY td.LastModifiedDate DESC);
GO 

-----------------------------------------------------

-- option 2: Filter on ROW_NUMBER()...
WITH 
    cte_AddRN AS (
        SELECT 
            td.Id,
            rn = ROW_NUMBER() OVER (PARTITION BY td.Category, td.Sex ORDER BY td.LastModifiedDate DESC)
        FROM
            #TestData td
        )
SELECT 
    arn.Id
FROM
    cte_AddRN arn
WHERE 
    arn.rn = 1;
GO 

-----------------------------------------------------

-- option 3: binary concatination...
SELECT 
    Id = CONVERT(INT, SUBSTRING(MAX(bv.bin_val), 4, 4))
FROM
    #TestData td
    CROSS APPLY ( VALUES (CONVERT(BINARY(3), td.LastModifiedDate) + CONVERT(BINARY(4), td.Id)) ) bv (bin_val)
GROUP BY 
    td.Category,
    td.Sex;
GO 

--====================================================

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Other option is to use correlated subquery :

select t.*
from table t
where t.LastModifiedDate = (select max(t1.LastModifiedDate) 
                            from table t1 
                            where t1.Category = t.Category and t1.Sex = t.Sex
                           );

Upvotes: 1

kgzdev
kgzdev

Reputation: 2885

We can get the solution by joining the same table with its grouped set:

SELECT MIN(T.Id)
FROM Table T
INNER JOIN (SELECT  Category, 
                    Sex,
                    MAX(LastModifiedDate) AS LastModifiedDate
            FROM Table
            GROUP BY Category, Sex) GT 
            ON GT.Category = T.Category
            AND GT.Sex = T.Sex
            AND GT.LastModifiedDate = T.LastModifiedDate
GROUP BY T.Category, T.Sex

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

You can also get it as

SELECT T.*
FROM
    (
      SELECT Sex,
             MAX([Last Modified Date]) [Last Modified Date],
             Category
      FROM T
      GROUP BY Sex,
            Category
    ) TT INNER JOIN T ON T.[Last Modified Date] = TT.[Last Modified Date]
WHERE T.Sex = TT.Sex
      AND
      T.Category = TT.Category;

Returns:

+----------+-----+---------------------+-------+
| Category | Sex | Last Modified Date  |  Id   |
+----------+-----+---------------------+-------+
|        7 |   2 | 06/09/2018 00:00:00 | 87135 |
|        7 |   1 | 27/12/2017 00:00:00 | 87568 |
+----------+-----+---------------------+-------+

Upvotes: 1

Related Questions