question

SQL-4608 avatar image
0 Votes"
SQL-4608 asked ErlandSommarskog answered

SQL Query help - Find the latest file name

Need help with query to find the latest record from the name. We store our folder file names in SQL table column which has the date. I would like to get the latest filename based on the date in the filename and store it in variable. In this particular case, it should display me 'T3_Datemart_Control_File.csv_20210607_23h32m02'

 DECLARE @TableFileName TABLE
 (FileName varchar(100))
    
 insert into @TableFileName values ('T3_Datemart_Control_File.csv_20210407_23h02m06')
 insert into @TableFileName values ('T3_Datemart_Control_File.csv_20210506_23h01m03')
 insert into @TableFileName values ('T3_Datemart_Control_File.csv_20210607_23h32m02')
    
 select * from @TableFileName
sql-server-transact-sql
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.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
; WITH numbering AS (
   SELECT FileName, 
          row_number() OVER(ORDER BY right(FileName, 17) DESC) AS rowno
   FROM   @TableFileName
)
SELECT FileName
FROM   numbering
WHERE  rowno = 1
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.