SQL query help with distince max dates

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]

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

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.

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources