Aiden Aiden - 5 months ago 12
SQL Question

Select column value corresponding to MAX column value

I have a table Person, date is in

yyyymmDD
format and time is is
hhmmssSSS
format

ID NAME CREATEDDATE CREATEDTIME
1 A 20170205 135744300
2 B 20160205 113514368
3 C 20090205 163054942
4 A 20150205 135744301


Now I want to get count of each person and maximum createddate and CORRESPONDING createdTIME in a single query

I tried

SELECT NAME, COUNT (ID) AS COUNT, MAX(CREATEDDATE), MAX(CREATEDTIME)
FROM Person


but this gives me maximum date and maximum time from each column, it does not give me CREATEDTIME corresponding to MAX(CREATEDDATE)

e.g.

The query results is

NAME COUNT MAXCREATEDDATE CORRESPONDINGCREATEDTIME

A 2 20170205 135744301


The
CORRESPONDINGCREATEDTIME
should be
135744300


Please help me do it

Answer

You could do this simply with a subquery that finds the max date for each Name and perform an inner join.

Test Data

CREATE TABLE #Person (ID int, Name varchar(1), CreatedDate int, CreatedTime int)
INSERT INTO #Person (ID, Name, CreatedDate, CreatedTime)
VALUES
 (1,'A',20170205,135744300)
,(2,'B',20160205,113514368)
,(3,'C',20090205,163054942)
,(4,'A',20150205,135744301)

Query

SELECT
a.Name
,b.CtName CountName
,a.CreatedDate
,a.CreatedTime
FROM #Person a
JOIN    (
            SELECT 
            Name
            ,COUNT(Name) CtName
            ,MAX(CreatedDate) MaxDate 
            FROM #Person 
            GROUP BY Name
        ) b
ON a.Name = b.Name
AND a.CreatedDate = b.MaxDate

Result

Name    CountName   CreatedDate CreatedTime
A       2           20170205    135744300
B       1           20160205    113514368
C       1           20090205    163054942
Comments