Select MAX value from each group in SQL Server

sat
 
on May 04, 2021 03:57 AM
624 Views

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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 05, 2021 12:25 AM

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