MySQL GROUP BY with MIN - incorrect column data

Question

I have looked here: Selecting all corresponding fields using MAX and GROUP BY and similar pages on SO but I cannot seem to get all my fields to line up properly.

I feel like I'm at the cusp of figuring this out but maybe I'm heading down the wrong path and need to look at this differently.

What I want is the unit with the lowest rent per property name per bedroom count that have the merge flag set to 1.

My SQL Fiddle: http://sqlfiddle.com/#!2/881c41/2

All rental units with merge = 1 query result

The image above was obtained with this query:

SELECT ru.id, run.name, ru.rent, ru.bedrooms
FROM rental_units AS ru
JOIN rental_unit_names AS run
on run.id = ru.name_id
WHERE run.merge = 1
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC

Rental units with merge = 1 grouped by property name and bedrooms by min value query result

The image above is the result of this query:

SELECT ru.id, run.name, ru.rent, MIN(ru.rent) AS min_rent, ru.bedrooms
FROM rental_units AS ru
JOIN rental_unit_names AS run
on run.id = ru.name_id
WHERE run.merge = 1
GROUP BY ru.name_id, ru.bedrooms
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC, ru.id ASC

For the most part all looks fine and dandy until you look at row 4. The rent values do not line up and the id should be 6 not 5.

The image below is my desired result.

desired results

:: EDIT 1 ::

Do I need to create a linking table with 2 columns that has the rental unit id in one column and the rental unit name id in the other column? Or at least do this as a derived table somehow?

Ed Gibbs · Accepted Answer · 2013-06-20T21:09:44.563

1

In general, unless you're trying to perform some sort of MySQL "magic" you should always group by every non-aggregate, non-constant column in your SELECT list.

In your case, the best approach is to get a list of (name, # bedrooms, minimum rent), and then find all the rows that match these values - in other words, all rows whose (name, # bedrooms, rent) match the list with the minimum rent:

SELECT ru.id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)

This query will give all lowest-rent units by name/bedrooms. The sample data has ties for lowest in a couple of places. To include only one of the "tied" rows (the one with the lowest rental_units.id, try this instead - the only change is the MIN(ru.id) on the first line and the addition of an overall GROUP BY on the last line:

SELECT MIN(ru.id) AS ru_id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)
GROUP BY run.name, ru.rent, ru.bedrooms

edited Jun 20 '13 at 21:09

answered Jun 20 '13 at 20:01

Ed Gibbs

25,924
4
46
69

That returns all the rental units where the rental unit name is set to `merge = 1`. Your query is only different from my first query above in that there is a `min_rent` column. Your query with the SQL Fiddle: http://sqlfiddle.com/#!2/881c41/4. – hungerstar Jun 20 '13 at 20:09
Ah, I get it. I should have read your question more carefully. I'll post an update; this is doable. – Ed Gibbs Jun 20 '13 at 20:19
Can you try the query above in the Fiddle @hungerstar? It returns your expected results with two exceptions: ID #2 and ID #8 are also included because they're tied as lowest rent with ID #1 and ID #7 respectively. Is that close enough? – Ed Gibbs Jun 20 '13 at 20:36
That does work and I had a different query do the same thing but the ideal solution would remove the duplicates but maintain the order this query has when duplicates appear. – hungerstar Jun 20 '13 at 21:00
You can do that by selecting `MIN(ru.id)` instead of `ru.id` in the first line, then adding an overall `GROUP BY run.name, ru.rent, ru.bedrooms`. There's a new SQL Fiddle [here](http://www.sqlfiddle.com/#!2/881c41/42) that matches your results. – Ed Gibbs Jun 20 '13 at 21:07

score 1 · Answer 2 · edited May 23 '17 at 12:07

That is because the columns not included in the group by come from indeterminate rows. MySQL documentation is very clear on this point:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

Because I just answered this question on another post, I'm going to suggest that you look there.

EDIT:

Here is how you apply the substring_index()/group_concat() method to your query:

SELECT substring_index(group_concat(ru.id order by rent), ',', 1) as id,
       run.name, MIN(ru.rent) AS min_rent, ru.bedrooms
FROM rental_units ru JOIN
     rental_unit_names run
     on run.id = ru.name_id
WHERE run.merge = 1
GROUP BY ru.name_id, ru.bedrooms
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC, ru.id ASC

I get that it is free to choose the values. How do you rectify it though? — hungerstar, Jun 20 '13 at 20:15
Ah. Very clever! My database isn't too big so I'm thinking this won't be too much of a performance hit using these extra functions? — hungerstar, Jun 20 '13 at 21:06
One issue. The first row should have and `id` of 1. This results in `id` of 2. — hungerstar, Jun 20 '13 at 23:52

cosmos · Answer 3 · 2013-06-20T21:10:12.927

SELECT min(ru.id) as id, run.name, ru.rent, ru.rent AS min_rent, ru.bedrooms
FROM rental_units AS ru
JOIN rental_unit_names AS run
on run.id = ru.name_id
WHERE run.merge = 1
and ru.rent = 
(select min(ru1.rent) from rental_units AS ru1
JOIN rental_unit_names AS run1
on run1.id = ru1.name_id
where run.name = run1.name
and ru.bedrooms = ru1.bedrooms
and run1.merge = 1)
group by run.name, ru.rent,min_rent, ru.bedrooms
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC, ru.id ASC;

Works PERFECT..!!

score 1 · Answer 4 · answered Jun 20 '13 at 21:15

Your query gives wrong results for the reason explained in mysql group-by extensions.

You can try putting your group by part in a subquery, then join back to the same table to get other hidden columns you might need (like id) and finally join to names table to get room name. You resolve ties by using lowest id for the self join.

SELECT ro.id, run.name, ro.rent, ro.bedrooms
FROM 
( SELECT name_id, bedrooms, MIN(rent) AS cheapest_rent
  FROM rental_units 
  GROUP BY name_id, bedrooms ) AS ru
JOIN rental_units ro
ON ro.id = ( SELECT ri.id FROM rental_units ri
              WHERE ri.name_id = ru.name_id
              AND ri.bedrooms = ru.bedrooms
              AND ri.rent = ru.cheapest_rent
              ORDER BY ri.name_id, ri.bedrooms, ri.rent, ri.id
              LIMIT 1 )
JOIN rental_unit_names run ON ro.name_id = run.id
WHERE run.merge = 1
ORDER BY run.name ASC, ro.bedrooms ASC, ro.rent ASC

Sqlfiddle here.

Note the slight change in the schema, I added an index on ( name_id, bedrooms, rent ) to help both with grouping and self-join (check execution plan on sqlfiddle) , though due to how mysql optimizer works, to use it for the join this awkward order by inside join condition is required. This is a fast solution for even quite a big table. You might also consider adding index on merge if it is selective enough.

MySQL GROUP BY with MIN - incorrect column data

4 Answers4