I have one table that contains one column of datetime, now I like to run a select statement, let me try to give some values below for better understanding.
drop table #temp
go
create table #temp
(
Number1 int
,datetime1 datetime
, alphanumericID varchar(100)
)
go
insert into #temp select 1234, '2018-09-18 20:13:00','AZ47T'
insert into #temp select 4567, '2018-09-18 21:13:00','ZTv487'
insert into #temp select 1234, '2018-09-18 22:13:00','45ZTv'
go
SQL Script
SELECT a.number1,
b.alphanumericid
FROM (SELECT number1,
Max(datetime1) AS datetime1
FROM #temp
GROUP BY number1) a
JOIN #temp b
ON a.number1 = b.number1
AND a.datetime1 = b.datetime1
go
SELECT *
FROM (SELECT number1,
CASE
WHEN datetime1 = Max(datetime1)
OVER(
partition BY number1 ) THEN alphanumericid
ELSE NULL
END AS alpha
FROM #temp) a
WHERE a.alpha IS NOT NULL
go
create table #temp
(
aNumber int
,aDatetime datetime
,alphanumericID varchar(100)
);
insert into #temp(aNumber,aDatetime,alphaNumericID)
select 1234, '2018-09-18 20:13:00','AZ47T' UNION ALL
select 4567, '2018-09-18 21:13:00','ZTv487' UNION ALL
select 1234, '2018-09-18 22:13:00','45ZTv';
;WITH cteSource AS
(SELECT aNumber,aDatetime,alphaNumericID
,ROW_NUMBER() OVER(PARTITION BY aNumber ORDER BY aDatetime DESC) as rn
FROM #temp
)
SELECT aNumber,alphaNumericID
FROM cteSource as s
WHERE s.rn =1
Thank you for the help, now I ran the same query on actual data, and I have like half billion records and query is running and not coming up with the results. Is there any optimize way to do it? and if I like to include more columns in result set what should I suppose to do.
I was running this query but now I like to include "alphanumericID" column which is a string column.
select anumber, count(col2), sum(col3) from #temp
group by anumber
if I write the same query as above as the table is not in my schema so I cannot create indexes, I have just read only user. so is there any way I can accommodate my above requirement in this query?
alphaNumericID value where datetime against "anumber" is maximum.
Actually I have to use group by clause on "anumber" column because I have to aggregrate few other columns but in "AlphaNumericID" column I want single value based on MAX(DateTime). I am writing below query. I just need to add "AlphaNumericID"
select anumber, sum(col1), count(col2) from #temp
group by anumber
create table #temp
(
aNumber int
,aDatetime datetime
,alphanumericID varchar(100)
,col1 int
,col2 int
);
insert into #temp(aNumber,aDatetime,alphaNumericID,col1,col2)
select 1234, '2018-09-18 20:13:00','AZ47T',42,1 UNION ALL
select 4567, '2018-09-18 21:13:00','ZTv487',58,1 UNION ALL
select 1234, '2018-09-18 22:13:00','45ZTv',24,1;
;WITH cteSource AS
(SELECT aNumber,alphaNumericID
,ROW_NUMBER() OVER(PARTITION BY aNumber ORDER BY aDatetime DESC) as rn
,SUM(col1) OVER(PARTITION BY aNumber ORDER BY aDatetime ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS sumCol1
,COUNT(col2) OVER(PARTITION BY aNumber ORDER BY aDatetime ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS cntCol2
FROM #temp
)
SELECT aNumber,alphaNumericID,sumCol1,cntCol2
FROM cteSource as s
WHERE s.rn =1;
output:
aNumber
alphaNumericID
sumCol1
cntCol2
1234
45ZTv
66
2
4567
ZTv487
58
1
SELECT a.aNumber
,b.alphanumericid
,a.SumCol1
,a.CntCol2
FROM (SELECT aNumber
,Max(aDatetime) AS MaxDatetime
,sum(col1) as SumCol1
,count(col2) as CntCol2
FROM #temp
GROUP BY aNumber) a
INNER JOIN #temp b
ON a.aNumber = b.aNumber
AND a.MaxDatetime = b.aDatetime ;