I'm using the following query to find a list of records by the latest date per category. So if there were four categories I would need a list of four categories with their latest corresponding dates. The category field is col5 and the date field is col6. I've got the date sorted our but not the category. It currently only returns the latest date no matter what cateogry and only returns 1 record. I'm not sure how to get it to return the dates for each unique category.
DB=postgresql 7.4 Any help would be appreciated!
WHERE (((tbl42.col3)=recordid7) AND ((tbl42.col12)=0)) and tbl42.col6 = (select max(col6) from tbl42)
ORDER BY tbl42.col6 DESC