Getting the correct row data when using MySQL aggregate function MIN?

Question

Now, as I understand when you use aggregate functions such as AVG, SUM etc you have to keep in mind that any other fields you SELECT that aren't also involved in an aggregate function will be indeterminate, for example:

SELECT AVG(amount), name, desc FROM some_table;

I understand this and this is because the value coming from the aggregate function isn't tied to any one row and hence the other fields selected are indeterminate.

However, if you use a different type of aggregate function such as MIN or MAX where what they retrieve is tied to a certain row then is it safe to assume that any other fields selected that aren't within an aggregate function can be determined? ... as the result would be tied to a specific row of data unlike the other aggregate function results?

For example:

SELECT MIN(media_id),
       auction_id,
       media_url
FROM   auction_media
WHERE  auction_id IN( 119925, 124660, 124663, 129078,
                      129094, 134395, 149753, 152221,
                      154733, 154737, 154742, 157694,
                      161411, 165965, 165973 )
       AND media_type = 1
       AND upload_in_progress = 0
GROUP  BY auction_id;

If I am right in my thinking this would always return the correct media_url right?

Answer 1

However, if you use a different type of aggregate function such as MIN or MAX where what they retrieve is tied to a certain row then is it safe to assume that any other fields selected that aren't within an aggregate function can be determined?

Nope. For one, multiple rows can have the min or max value; for another, there is nothing stopping a query from selecting MIN(a), MAX(a), AVG(a), and SUM(a) all at once (and I highly doubt MySQL would over-complicate it's query engine to take advantage of "if the query has only one aggregate...")

---

Note: I am fairly certain the only reason MySQL originally even allowed such queries was for short hand in situations like:

SELECT a.*, SUM(b.X)
FROM a INNER JOIN b ON a.PK = b.a_PK
GROUP BY a.PK;

where the query author knows the non-aggregated fields are can be determined by virtue of the grouping, not the aggregated value(s).

Answer 2

MIN and MAX is no more tied to any row than AVG or SUM is. All 4 of them are the result of aggregating multiple rows, whether all rows (like you first query), or the rows in a group (like your second query).

If I am right in my thinking this would always return the correct media_url right?

No. What if your data is:

auction_id   media_id   media_url
119925       3          http://google.com
119925       5          http://yahoo.com
119925       3          http://bing.com

Your query SELECT MIN(media_id), auction_id, media_url GROUP BY auction_id would return 3 for MIN(media_id), and 119925 for auction_id, but what media_url would it return?

media_url is still indeterminate.

You see, there is nothing in the data that says that media_url is in any way related to media_id.

You might (think you) know that the denormalized media_url is always the same for a particular media_id, but that doesn't matter to the SQL engine.

Answer 3

No. The unaggregated columns (that are not in the group by) in an aggregation query come from arbitrary and indeterminate rows. This awkward behavior is why the syntax is not allowed in most databases and why the most recent versions of MySQL "turn-it-off" by default. So your query would return an error.

Here is one way to do what you want:

SELECT am.*
FROM auction_media am
WHERE auction_id IN (119925, 124660, 124663, 129078,
                      129094, 134395, 149753, 152221,
                      154733, 154737, 154742, 157694,
                      161411, 165965, 165973 ) AND
      media_type = 1 AND upload_in_progress = 0 AND
      media_id = (SELECT MIN(am2.media_id)
                  FROM auction_media m2
                  WHERE m2.auction_id = m.auction_id AND m2.media_type = m.media_type AND m2.upload_in_progress = m.upload_in_progress
                 );

For performance you want an index on auction_media(auction_id, media_type, upload_in_progress, media_id) and auction_media(media_type, upload_in_progress, auction_id).