Mysql query in drupal database - groupwise maximum with duplicate data
- by nselikoff
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.
