Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 SQL query help with distince max dates

Author  Topic 

BillEdd
Starting Member

12 Posts

Posted - 2011-09-22 : 20:11:04
I have a table that has some repeated file names and correspoding dates. Each day a file's data is imported I capture the file name and the date

Example
File1 9/1/2011
File2 9/1/2011
File1 9/2/2011
File2 9/2/2011
File1 9/3/2011
File2 9/4/2011
File1 9/6/2011
File2 9/4/2011


I am trying to write a query that will return each unique file name and the last time that file was imported. My result will be this

File1 9/6/2011
File2 9/4/2011

Thanks in advance for our help

Bill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-22 : 20:21:50
[code]
select filename, max(import_date)
from yourtable
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

BillEdd
Starting Member

12 Posts

Posted - 2011-09-23 : 11:33:47
Thanks for your reply but it did not work. Here is my query and its error msg:

select XLS_File_Name_Imported, max(Date_XLS_File_Created)
from tbl_MOW_EXCEL_File_Import_Results

Column 'tbl_MOW_EXCEL_File_Import_Results.XLS_File_Name_Imported' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Again, here is my sample table

Example
XLS_File_Name_Imported Date_XLS_File_Created
File1 9/1/2011
File2 9/1/2011
File1 9/2/2011
File2 9/2/2011
File1 9/3/2011
File2 9/4/2011
File1 9/6/2011
File2 9/4/2011


I am trying to write a query that will return each unique file name once and the last time that file was imported. My result will be this

File1 9/6/2011
File2 9/4/2011

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-09-23 : 11:50:59
select XLS_File_Name_Imported, max(Date_XLS_File_Created)
from tbl_MOW_EXCEL_File_Import_Results

GROUP BY XLS_File_Name_Imported

AGGREGATE Functions MUST have a GROUP BY clause listing all fields to be AGGREGATED.
Go to Top of Page

BillEdd
Starting Member

12 Posts

Posted - 2011-09-23 : 12:04:38
For the purposes of the archives I found my solution and am posting it:

select distinct XLS_File_Name_Imported, max(Date_XLS_File_Modified)
from tbl_MOW_EXCEL_File_Import_Results
group by XLS_File_Name_Imported.

Thanks to all who attempted to help

Bill
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-24 : 09:50:09
the DISTINCT is not needed because of GROUP BY


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

newwaysys
Starting Member

9 Posts

Posted - 2015-04-13 : 06:00:24
select distinct XLS_File_Name_Imported, max(Date_XLS_File_Modified)
from tbl_MOW_EXCEL_File_Import_Results
group by XLS_File_Name_Imported.

Recently I generate Code 39 barcode in Reporting Service with this barcode tool (unspammed
Thanks to all who attempted to help
Go to Top of Page
   

- Advertisement -