Select column value corresponding to MAX column value

Question

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

what data types are the final two columns? date/time or varchar or int? — Rich Benner
– Rich Benner, Commented Jun 24, 2016 at 9:18

Rich Benner · Accepted Answer · 2016-06-24 09:29:33Z

2

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

edited Jun 24, 2016 at 9:29

answered Jun 24, 2016 at 9:23

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serg · Accepted Answer · 2016-06-24 09:55:02Z

1

One more, no joins or subquery

SELECT NAME, COUNT (ID) AS COUNT
  , MaxCREATEDDATE = MAX(cast(CREATEDDATE as bigint)*1000000000 + CREATEDTIME) /1000000000
  , CorrespTime = MAX(cast(CREATEDDATE as bigint)*1000000000 + CREATEDTIME) %1000000000
FROM Person
GROUP by NAME

answered Jun 24, 2016 at 9:55

Serg

22.8k5 gold badges23 silver badges50 bronze badges

2 Comments

Aiden Over a year ago

WOW that is a genius! Can you please explain the logic ? I think you have added both and then took a part of date and time

Serg Over a year ago

Exactly. First CREATEDDATE and CREATEDTIME are wrapped into single bigint. When max is found integer divisions ( / % ) are applied to get parts back.

DineshDB · Accepted Answer · 2016-06-24 09:23:53Z

1

Try this answer,

SELECT NAME, COUNT(ID) AS COUNT, max(CREATEDDATE), max(CREATEDTIME)
FROM Person
where CREATEDDATE = (select MAX(CREATEDDATE) from Person)
group by NAME

answered Jun 24, 2016 at 9:23

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

3 Comments

Andrey Korneyev Over a year ago

This is not the result OP wants. Your query will output only one person having created date equals to the maximum created date of all records.

Aiden Over a year ago

Yes that's right, I want each persons MAX DATE and corresponding time

Rich Benner Over a year ago

@DineshDB I thought that, but his result does give the example expected results that the OP posted.

neer · Accepted Answer · 2016-06-24 10:32:55Z

1

Try this also

    SELECT DISTINCT
       R.NAME,
       COUNT(1) OVER (PARTITION BY NAME),
       MAX(R.CREATEDDATE) OVER (PARTITION BY NAME ) ,
       MAX(P.CREATEDTIME) OVER (PARTITION BY NAME )
    FROM
        Person

edited Jun 24, 2016 at 10:32

answered Jun 24, 2016 at 9:32

neer

4,0926 gold badges22 silver badges34 bronze badges

2 Comments

neer Over a year ago

I think Now better

paparazzo Over a year ago

It is still max time. Exactly what OP said was a problem. See my answer.

Andrey Korneyev · Accepted Answer · 2016-06-24 09:18:06Z

0

You can calculate count and max(CREATEDDATE) in inner query and then join back to your table to get time according to max date for each person:

select
   R.NAME,
   R.COUNT,
   R.CREATEDDATE,
   P.CREATEDTIME
from
  (
    SELECT NAME, COUNT (ID) AS COUNT, MAX(CREATEDDATE) as CREATEDDATE
    FROM Person
    group by NAME
  ) as R
  left outer join Person as P on P.NAME = R.NAME and P.CREATEDDATE = R.CREATEDDATE

answered Jun 24, 2016 at 9:18

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Comments

paparazzo · Accepted Answer · 2016-06-24 13:28:46Z

0

select * from 
(
SELECT *,
       COUNT(1)     OVER (PARTITION BY NAME) as count,
       row_number() OVER (PARTITION BY NAME order by CREATEDDATE desc) as rn
 FROM  Person
) tt
where tt.rn = 1

edited Jun 24, 2016 at 13:28

answered Jun 24, 2016 at 10:19

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

Select column value corresponding to MAX column value

6 Answers 6

Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related