a way to improve this query?

[pandatime]

I can't do a group by because it returns *all* the rows grouped by file_dt. I just want the row with the max(file_dt).

Thanks

select convert(varchar,file_dt, 110) + ', ' + convert(varchar, tran_count) 
from myTable
where id = 1 
and file_dt = (select max(file_dt) from myTable where id = 1 and file_type = 'X') 
and file_type = 'X'

 

[xxing]

Hi pandatime

The following link may help

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

regards
Mark

 

[pandatime]

Good article, thanks, but I didn't see anything relating to my particular query.

 

[NastiaMurzin]

Here is the explanation of the max function.
use tableschema;
go
select max(columnname) from tableschema.tablename;
go

 

[pandatime]

Nevermind. What I was thinking of was TOP 1 with ORDER BY DESC. That would eliminate the need for the subquery to find the max date.

 

[NastiaMurzin]

order by desc
or
order by asc
would do it. You are right! If you know how to use it.

 

[NastiaMurzin]

Do you need to know the file size? Or do you need to know the max value of the values in a column?