I have a "revisions" table that keeps track of versions of an item:

item_id | version_id | approved
3 | 334 | 0
3 | 333 | 1
3 | 332 | 1
8 | 556 | 1
8 | 555 | 1
9 | 654 | 0
9 | 653 | 1

I would like to get a list of Items whose most recent version has not been approved (approved == 0). So in the example data above, my result set would be:
item_id
3
9


Is this possible to do in a single query? This was my first guess:
SELECT MAX(version_id), item_id, approved FROM revisions GROUP BY item_id HAVING approved = 0;

However, I'm 99% positive this is not correct.

Any thoughts?

i'm 100% positive

this is a classic example of "rows holding the groupwise maximum" (if you google this, you will see some interesting results)
by the way, it's nice to see some more iNet people on their own forum

Thanks Rudy, I was hoping there would be a way to avoid sub-queries, but I guess there probably is not, and a derived table is something I hadn't considered. I'm picking through those search results now.

I was a member here well before I started at iNET (that was part of the appeal of the job!). When I've reached the edge of my abilities (which I do abnormally often it seems), it is a great feeling to be able to tap into one of our own communities for expert advice...

It may be time to convince iNET to buy me a copy of your Simply SQL book

Thanks again!

Here is the query I ended up using (slight modification of Rudy's suggestion)