how to sql query to compare data 1

[mdwu]

hi All,

I need to write a query taht can compare data from 1 table.
Basically, every morning, the SQL server generates 100 different reports and publish it. I need to know for each date, which file is publish last. Since the files is diffferent in size, so file1 coould be last for today while file 2 can be last the next day.

For ex:
Filename, publish datetime
file1, 1/1/01 7am
file1, 1/2/01 8am
file1, 1/3/01 9am
file2, 1/1/01 10am
file2, 1/2/01 7am
file2, 1/3/01 5am

Thanks

 

[SQLSister]

Select filename, max([publish datetime] from Table1
Group by filename

BTW, try to avoid putting spaces in your field names, they require special handling (the [] above) every time you use them.

Questions about posting. See faq183-874

 

[mdwu]

Thanks SQLSsister..

I just try your query, I love it. But I might have explain it wrong. but it's a good query That I can use for some other project.

The query you provide only shows the max date for the particular file. But I wanted to show the file per day that was last.

so if let say from sept 1 to Oct 30. for each day, which file is last.

ex.
9/1/01 file1
9/2/01 file5
9/3/01 file10.....etc

 

[SQLSister]

Then you will have to group by publish date without the time component. USe the convert function to convert it to a varchar field containing only the date. Looking up using cast and convert in BOL for information on how to do this.

Questions about posting. See faq183-874

 

[mdwu]

the problem is that the time is what's critical.
ex.

file1 9/1/01 8:00am
file2 9/1/02 8:01am

then file2 should be the result.

 

[SQLSister]

Time is crital in the max part of the statement, I was suggesting you use a converted date field in the group by.

Questions about posting. See faq183-874