CODEWITHSUNDEEP
sqlsql-servert-sql
Aiden
Aiden

Reputation: 460

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

Upvotes: 2

Views: 109

Answers (6)

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 0

neer
neer

Reputation: 4082

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

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

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

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

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

Upvotes: 0

Related Questions