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.
| 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 |
|
|
|
|
|
|
SQL query help with distince max dates