Hi everyone,
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.
Number, datetime, alpha-numeric ID
1234, 2018-09-18 20:13:00, AZ47T
4567, 2018-09-18 21:13:00, ZTv487
1234, 2018-09-18 22:13:00, 45ZTv
In my query I like to group on "Number" column and in result like to get "alpha-numeric ID" where "datetime" is maximum.
hi
i hope this is what you are looking for
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
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
Another Way to get the same result
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
Another approach:
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
output :
| aNumber | alphaNumericID |
|---|---|
| 1234 | 45ZTv |
| 4567 | ZTv487 |
dbfiddle here
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.
Thanks again
An index on Number and Datetime
CREATE INDEX idx_Name ON table
(aNumber,aDatetime DESC)
ps: I do not know , if the combination is unique.If combination is unique, you should create as :
CREATE UNIQUE INDEX idx_Name ON table
(aNumber,aDatetime DESC)to include more columns :
;WITH cteSource AS
(SELECT aNumber,aDatetime,alphaNumericID,col1,col2,...
,ROW_NUMBER() ...
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.
Sorry, your request is not clear to me.
On your sample data, show the desired output.
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
Let me put sample data again 
Number, datetime, alpha-numeric ID, col1, col2
1234, 2018-09-18 20:13:00, AZ47T, 42, 1
4567, 2018-09-18 21:13:00, ZTv487, 58, 1
1234, 2018-09-18 22:13:00, 45ZTv , 24, 1
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 ;
dbfiddle here