Grouping query while providing extra selects

Question

I have the following query

select t1.file_name, 
       max(create_date), 
       t1.id 
 from dbo.translation_style_sheet AS t1 
GROUP BY t1.file_name

I'd like to add the id to the select, but everytime I do this, it returns all results rather than my grouped results

my goal is to return

pdf | 10/1/2012 | 3
doc | 10/2/2012 | 5

however my query is returning

pdf | 9/30/2012 | 1
pdf | 9/31/2012 | 2
pdf | 10/1/2012 | 3
doc | 10/1/2012 | 4
doc | 10/2/2012 | 5

Anybody know what I'm doing wrong?

Answer 1

If you are guaranteed that the row with the max(create_date) also has the largest id value (your sample data does but I don't know if that is an artifact), you can simply select the max(id)

select t1.file_name, max(create_date), max(t1.id) 
  from dbo.translation_style_sheet AS t1 
 GROUP BY t1.file_name

If you are not guaranteed that, and you want the id value that is from the row that has the max(create_date) to be returned, you can use analytic functions. The following will work in Oracle though it is unlikely that less sophisticated databases like Derby would support it.

select t1.file_name, t1.create_date, t1.id
  from (select t2.file_name,
               t2.create_date,
               t2.id,
               rank() over (partition by t2.file_name 
                                order by t2.create_date desc) rnk
          from dbo.translation_style_sheet t2) t1 
  where rnk = 1

You could also use a subquery. This is likely to be more portable but less efficient than the analytic function approach

select t1.file_name, t1.create_date, t1.id
  from dbo.translation_style_sheet t1
 where (t1.file_name, t1.create_date) in (select t2.file_name, max(t2.create_date)
                                            from dbo.translation_style_sheet t2
                                           group by t2.file_name);

or

select t1.file_name, t1.create_date, t1.id
  from dbo.translation_style_sheet t1
 where t1.create_date = (select max(t2.create_date) 
                           from dbo.translation_style_sheet t2
                          where t1.file_name = t2.file_name);