Showing posts with label group by. Show all posts
Showing posts with label group by. Show all posts

Wednesday, May 27, 2015

Select one unique record from DB table with the latest date

I have a file_store table like below.



I would like to select the latest records for each filetype_id.



The SQL to do this can be like this.

 SELECT f.file_id,f.FILETYPE_ID, f.file_obj_l,max_date  
 FROM FILE_STORE f inner JOIN   
 (SELECT FILETYPE_ID, max(f1.UPDATETS) as max_date FROM FILE_STORE f1  
 where FILETYPE_ID in (1, 2, 3)   
  group by FILETYPE_ID  
 ) a  
 on a.FILETYPE_ID = f.FILETYPE_ID and a.max_date = f.UPDATETS