noob needs sql help

[johnjohn2]

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!

SELECT
tbl42.col1,
tbl42.col3,
tbl42.col4,
tbl42.col5,
tbl42.col6,
tbl42.col7,
tbl42.col8,
tbl42.col10,
tbl42.col11,
tbl42.col12
FROM tbl42
WHERE (((tbl42.col3)=recordid7) AND ((tbl42.col12)=0)) and tbl42.col6 = (select max(col6) from tbl42)
ORDER BY tbl42.col6 DESC

[r937]

FROM tbl42 as foo
WHERE col3 = recordid7
  AND col12 = 0
  and col6 = 
      ( 
      select max(col6) 
        from tbl42
       where col5 = foo.col5
      )

you may also want to remove the (unnecessary) table prefixes on all the columns in the SELECT and ORDER BY lists, or else change them to foo.

[johnjohn2]

Thanks for the help! That was messing me up for a while.

FROM tbl42 as foo
WHERE col3 = recordid7
  AND col12 = 0
  and col6 = 
      ( 
      select max(col6) 
        from tbl42
       where col5 = foo.col5
      )

you may also want to remove the (unnecessary) table prefixes on all the columns in the SELECT and ORDER BY lists, or else change them to foo.