Get only items with max date 1

[oppcos]

Hi,
I have a table with company #, items, and dates. An example might be:

co_num  |  items  |  dates
--------------------------
1         abc        2007-04-06
1         xyz        2007-04-06
1         ghi        2006-12-18
...
2         jkl        2007-01-01
2         mno        2006-12-18
...

I want to select the items per company but only those with the max date (most recent) for that company. So the result from the example table would be:

1  abc  2007-04-06
1  abc  2007-04-06
2  jkl  2007-01-01

I'm stuck figuring out how to start. I've tried a few combinations of max(dates) in the where clause but that gives me only items from the highest date in the table, not the highest date per company.

Any suggestions? Thanks

 

[r937]

select t.co_num  
     , t.items  
     , t.dates
  from daTable as t
 where t.dates =
       ( select max(dates)
           from daTable
          where co_num = t.co_num )

r937.com | rudy.ca

 

[oppcos]

Cool, I didn't realize a query like that would relate each row with what must be a new select for each in the where clause by doing the co_num = t.co_num. Thanks for that great example.