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

You may be better off using node_load() to get the load object rather than trying to write a query yourself.

Or even use views to do what you need.

The reason for doing this over writing a query for yourself is that Drupal and it's modules sit together as a framework. Most of the time you will want to use that framework to do what you want rather than side stepping it to write your own query. In future if you upgrade Drupal or a module node_load() will still work but your code may not.

Answer 2

Assuming that (nid, vid) combination is unqiue:

SELECT  m.*
FROM    (
        SELECT  nid, MAX(vid) AS mvid
        FROM    mytable
        GROUP BY
                nid
        ) q
JOIN    mytable m
ON      (m.nid, m.vid) = (q.nid, q.mvid)

Answer 3

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.