Can't get my head around this one...
SteveTurnbull - July 16, 2008 - 15:34
It's mysql question but specific to Drupal. Let's say I have a node plus its revisions:
vid___nid___field1___field2
55___10______x______a
56___10______x______b
57___10______x______c
I search for nodes where field2=b so I get the one with vid=56 (and nid=10), but at this point I'm only fetching NIDs. At a later point I fetch the node using node_load(10) ... and I get the one with vid=57 and field2 = 'c'.
But really I don't want either, I only want records with the highest vid of any given nid. (So in this example I'd get nothing, the highest vid of nid=10 has field2 of 'c').
I'm sure this is simple but I just can't see it.
Hope you can help.
Steve
If your version of mysql
If your version of mysql supports sub queries this should help
SELECT * FROM fun WHERE vid IN (SELECT MAX(vid) as max_vid FROM fun f GROUP BY nid) AND field2 = 'blue'Note you need to replace the table name (fun) twice with your actual table name (once in query, once in sub-query)
Thanks for looking at that,
Thanks for looking at that, in fact I forgot to answer my own question which was the solution you suggest.