Select MAX value from each group in SQL Server

Last Reply 7 days ago By dharmendr

Posted 8 days ago

Dear all,

I want to select top 1 max date record from each Companies

I have below sample table in mssql database:

Companies         DC

Co1                        26/04/21

Co2                        26/04/21

Co3                        25/04/21

Co4                        25/04/21

Co2                        25/04/21

Co3                        25/04/21

Co1                        24/04/21

Co1                        22/04/21

Co2                        22/04/21

Co3                        22/04/21

Co4                        21/04/21

Co2                        20/04/21

Co3                        20/04/21

Co5                        19/03/21

Co1                        19/02/21

What must be my select statement to fetch below gridview:

Name                    DC

Co1                        26/04/21

Co2                        26/04/21

Co3                        25/04/21

Co4                        25/04/21

Co5                        19/03/21

Next I also need to find out how many companies are there (count) where DC is more than 30 days old. In above case it would be 1.

Pl help. Sat

Posted 7 days ago

Hi sat,

Refer below query.

SQL

CREATE TABLE #Company(Companies CHAR(3),DC VARCHAR(10))
INSERT INTO #Company VALUES('Co1','26/04/21')
INSERT INTO #Company VALUES('Co2','26/04/21')
INSERT INTO #Company VALUES('Co3','25/04/21')
INSERT INTO #Company VALUES('Co4','25/04/21')
INSERT INTO #Company VALUES('Co2','25/04/21')
INSERT INTO #Company VALUES('Co3','25/04/21')
INSERT INTO #Company VALUES('Co1','24/04/21')
INSERT INTO #Company VALUES('Co1','22/04/21')
INSERT INTO #Company VALUES('Co2','22/04/21')
INSERT INTO #Company VALUES('Co3','22/04/21')
INSERT INTO #Company VALUES('Co4','21/04/21')
INSERT INTO #Company VALUES('Co2','20/04/21')
INSERT INTO #Company VALUES('Co3','20/04/21')
INSERT INTO #Company VALUES('Co5','19/03/21')
INSERT INTO #Company VALUES('Co1','19/02/21')

SELECT Companies,MAX(DC) DC,
(SELECT COUNT(DC) FROM #Company c2 WHERE DATEDIFF(DAY,TRY_PARSE(DC AS DATE USING 'en-in'),GETDATE()) > 30 AND c1.Companies=c2.Companies) Old
FROM #Company c1
GROUP BY Companies

DROP TABLE #Company

Output

Companies DC Old
Co1 26/04/21 1
Co2 26/04/21 0
Co3 25/04/21 0
Co4 25/04/21 0
Co5 19/03/21 1