SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 SQL query help with distince max dates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BillEdd
Starting Member

8 Posts
Posted - 09/22/2011 :  20:11:04  Show Profile  Reply with Quote
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)

Singapore
16745 Posts
Posted - 09/22/2011 :  20:21:50  Show Profile  Reply with Quote 

select filename, max(import_date)
from   yourtable


KH
Time is always against us
Go to Top of Page

BillEdd
Starting Member

8 Posts
Posted - 09/23/2011 :  11:33:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2915 Posts
Posted - 09/23/2011 :  11:50:59  Show Profile  Reply with Quote
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

8 Posts
Posted - 09/23/2011 :  12:04:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 09/24/2011 :  09:50:09  Show Profile  Visit webfred's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000