I'm working on a mysql query in a Drupal database that pulls together users and two different cck content types. I know people ask for help with groupwise maximum queries all the time... I've done my best but I need help.

This is what I have so far:

# the artists
SELECT
    users.uid,
    users.name AS username,
    n1.title AS artist_name
FROM users
LEFT JOIN users_roles ur 
    ON users.uid=ur.uid
INNER JOIN role r 
    ON ur.rid=r.rid 
    AND r.name='artist'
LEFT JOIN node n1 
    ON n1.uid = users.uid 
    AND n1.type = 'submission'
WHERE users.status = 1
ORDER BY users.name;

This gives me data that looks like:

uid username artist_name
1   foo      Joe the Plumber
2   bar      Jane Doe
3   baz      The Tooth Fairy

Also, I've got this query:

# artwork
SELECT
    n.nid, 
    n.uid,
    a.field_order_value
FROM node n
LEFT JOIN content_type_artwork a 
    ON n.nid = a.nid
WHERE n.type = 'artwork' 
ORDER BY n.uid, a.field_order_value;

Which gives me data like this:

nid uid field_order_value
1   1   1
2   1   3
3   1   2
4   2   NULL
5   3   1
6   3   1

Additional relevant info:

• nid is the primary key for an Artwork
• every Artist has one or more Artworks
• valid data for field_order_value is NULL, 1, 2, 3, or 4
• field_order_value is not necessarily unique per Artist - an Artist could have 4 Artworks all with field_order_value = 1.

What I want is the row with the minimum field_order_value from my second query joined with the artist information from the first query. In cases where the field_order_value is not valuable information (either because the Artist has used duplicate values among their Artworks or left that field NULL), I would like the row with the minimum nid from the second query.