return latest version of a drupal node

Question

I'm writing a drupal module, and I need to write a query that returns particular rows of one of my content_type tables. My query so far is:

SELECT DISTINCT pb.*, f.filepath FROM {content_type_promo_box} pb LEFT JOIN {files} f ON pb.field_promo_image_fid = f.fid

I realized as I was working that the table not only contains each cck field of this content type, it also contains multiple versions for each field. How do I limit my query to the rows that only contain values for the current versions of the nodes?

UPDATE: I need to clarify my question a little. I've been down the views path already, and I did think about using node_load (thanks for the answer, though, Jeremy!). Really, my question is more about how to write an appropriate SQL statement than it is about drupal specifically. I only want to return rows that contain the latest versions (vid is the greatest) for any particular node (nid). So here's an example:

-------------
| nid | vid |
-------------
| 45  |  3  |
| 23  |  5  |
| 45  |  9  |
| 23  |  12 |
| 45  |  36 |
| 33  |  44 |
| 33  |  78 |
------------- 

My query should return the following:

-------------
| nid | vid |
-------------
| 23  |  12 |
| 45  |  36 |
| 33  |  78 |
-------------

Make sense? Thanks!

Answer 1

The node table stores the current version of the node, and revision ids are unique across all content. This makes for a pretty simple query:

SELECT  m.*
FROM    
    {mytable} AS m
JOIN {node} AS n
ON m.vid = n.vid

If there is no content in {mytable} for the node, it will not be returned by the query; change to a RIGHT JOIN to return all nodes.