select latest date

Question

I have a table that has multiple date values and I want to select just the latest date
Table_Dates : Wkstn / Date_Created / Date_HWScan / Date_SWScan / Date_PatchScan / Date_CheckIn

So I'd like to have a query that will return Wkstn and a single date value that is the newest date.

Please advise if there's a way to complete this.

Answer 1

Please also check:

    CREATE TABLE #Table_Dates  
    (Wkstn VARCHAR(MAX), Date_Created DATE,Date_HWScan   
    DATE,Date_SWScan DATE,Date_PatchScan DATE,Date_CheckIn DATE)  
      
    INSERT INTO #Table_Dates VALUES  
    ('Wkstn','2021-12-14','2021-12-21','2021-12-01','2021-11-30','2021-11-21')  
      
    SELECT Wkstn,MAX(dDate) maxDate    
    FROM #Table_Dates  
    UNPIVOT (dDate FOR nDate IN ( Date_Created,Date_HWScan   
    ,Date_SWScan ,Date_PatchScan,Date_CheckIn)) as u  
    GROUP BY Wkstn  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Beside the answers in the thread that Nasreen pointed you to, here is a simpler solution, but which only works on Azure SQL Database and Azure SQL Managed Instance:

SELECT Wkstn, greatest(Date_Created, Date_HWScan, Date_SWScan, Date_PatchScan, Date_CheckIn)
FROM tbl

Answer 3

Hi @Steeves, Jason ,

There is a similar thread, link is given below. Hope this will help. Thanks!

https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

Answer 4

@Jason Steeves ,Please try this.

Select Wkstn,DateChkn --DateChkn = Replace Date Column based on requirement
From Table Where DateChkn = (Select Max(DateChkn) From Table)

Thanks.