question

jamesbennett-8006 avatar image
0 Votes"
jamesbennett-8006 asked MelissaMa-msft edited

Multiple Rows, choose latest date (MAX)

Can anyone help me complete my query so that I can:

Return unique records only by filtering out duplicates by selecting the latest date only?



I've have the following query that, if the source table contains multiple entries of the same Servername, returns duplicate rows.

 select  Servers.Servername, [Update Frequency], [Updaye frequency Type], dbo.WU_Servers.ServerName AS WU_Servers_Servername, dbo.WU_Servers.[Last Patch], dbo.WU_Servers.UniqueBatch, dbo.WU_Servers.dateTimeHit
 from Servers
 left join WU_Servers
 on dbo.Servers.Servername = dbo.WU_Servers.ServerName
 where [Update Frequency] = 60 and [Last Patch] < DATEADD(DAY, 30, GETDATE())

My left join is on Servername in each table and it is this being returned more than one if there is more than one entry in the WU_Servers.Servername table with the same name.

For each row returned there is a WU_Servers.[Last Patch] column and for duplicate rows I need only the latest date from the WU_Servers.[Last Patch] column.

As an example, this is the data being returned from my query:

126978-capture.png



You can see that the [Last Patch] column for the 2 records is different, one is for 2020-11-11 00:00:00.000 and one for 2020-11-12 00:00:00.000. It is the record with the latest date 2020-11-1*2* 00:00:00.000 that I need.

Thanks for looking!
Regards


sql-server-generalsql-server-transact-sql
capture.png (8.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

[Last Patch] < DATEADD(DAY, 30, GETDATE())

Where the last patch is older then 30 days in furture? That returns all, I guess.

Please post table design as DDL, some sample data as DML statement and the expected result.


0 Votes 0 ·

Hi @jamesbennett-8006,

Could you please provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @jamesbennett-8006,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Based on the query you provided, please refer below and check whether it is helpful to you.

 select Servername, [Update Frequency], [Updaye frequency Type],  WU_Servers_Servername, [Last Patch], UniqueBatch, dateTimeHit from (
 select  ROW_NUMBER() OVER (PARTITION BY Servers.Servername, [Update Frequency], [Updaye frequency Type], dbo.WU_Servers.ServerName,dbo.WU_Servers.UniqueBatch ORDER BY dbo.WU_Servers.[Last Patch] DESC) rn,
 Servers.Servername, [Update Frequency], [Updaye frequency Type], dbo.WU_Servers.ServerName AS WU_Servers_Servername, dbo.WU_Servers.[Last Patch], dbo.WU_Servers.UniqueBatch, dbo.WU_Servers.dateTimeHit
 from Servers
 left join WU_Servers
 on dbo.Servers.Servername = dbo.WU_Servers.ServerName
 where [Update Frequency] = 60 and [Last Patch] < DATEADD(DAY, 30, GETDATE()))a
 where rn=1

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.