Maybe I was just really tired from putting together my toolbox, but I couldn’t figure out why my initial query didn’t work last night when I was debugging the SQL for the Extend Firefox Finalists Page. The original SQL query would not sort the result set based on v.vid DESC and I would receive the same v.version everytime (the smallest one).
I fixed it with a subselect to ensure we were pulling the max v.vid per main.id, but the original query still bugs me. So if anybody has any comments or ideas about why the initial one was borked, I’m curious to find out.
If you stare at something long enough, the solution disappears and instead you find yourself staring at a pretty unicorn.
morgamic 
You select DISTINCT, which makes the v.vid column not relevant at all, as you have filtered out distinct rows made up by the columns in the SELECT. When using DISTINCT, only the columns selected are relevant. So the first query can never work.
UNICORNS!!! 🙂
What Kai says sounds reasonable. In MSSQLServer2000 if you are using the distinct keyword and specify order by with a column that is not in the select list it will throw an error.
Yeah, brain fart. It was the incorrect use of a GROUP BY clause where I was not grouping by all fields that weren’t using an aggregate function (in fact, I wasn’t using one at all in the first query).
The DISTINCT is also unnecessary in the second one.
Thanks for your comments. 🙂