Query Help

Transact-SQL
#1

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.

#2

hi

i hope this is what you are looking for

Create Data Script 
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
Result 
![image|677x246](upload://j4axQ85fef2tQQIPf8Whwergwxt.png)

Another Way to get the same result

Another Way SQL 
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
#3

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

#4

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

#5

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)
#6

to include more columns :

;WITH cteSource AS
(SELECT aNumber,aDatetime,alphaNumericID,col1,col2,...
,ROW_NUMBER() ...

#7

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.

#8

Sorry, your request is not clear to me.
On your sample data, show the desired output.

#9

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 :slight_smile:

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

#11 
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