how to get latest file

how to get the name of the latest file of a particular directory
thru query
i.e
i have files in a directory
1130am.txt
11.45am.txt
1200am.txt
my query should return 1200am.txtOriginally posted by vrsrinivas
how to get the name of the latest file of a particular directory
thru query

i.e
i have files in a directory

1130am.txt
11.45am.txt
1200am.txt

my query should return 1200am.txt

Assuming that all your file names are prefixed with a time stamp then you should be able to use the MAX() function to return the desired record.

create table #test (theFileName varchar(20))

insert into #test (theFileName) values ('1130am.txt')
insert into #test (theFileName) values ('11.45am.txt')
insert into #test (theFileName) values ('1200am.txt')

select max(theFileName) from #test|||I'm thinking the hard part is getting the info from the directory...

Delete From Ledger_Folder

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

Delete From Ledger_Folder_Parsed

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')