SQL join with group by and MIN in sql query

Question

So, I've got this 3 table relationship where "lampara_generica" gets substituted by "lamapara_nitide" Tables ralationship

Now, I only want the better substitute, the one which have the lower value in "lampara_nitide" for the column "w_real".

Im doing the next query and the resultset is the expected:

SELECT lampara_nitide_has_lampara_generica.* , lampara_nitide.* 
FROM lampara_nitide_has_lampara_generica 
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id

+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+
| lampara_nitide_id | lampara_generica_id | id | nombre | w_teorico | w_real    | horas_mantenimiento | coste_mantenimiento |
+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+
|                 1 |                   1 |  1 | 4      | 4.0000000 | 0.5000000 |                   4 |                   4 |
|                 2 |                   1 |  2 | 5      | 5.0000000 | 0.2500000 |                   5 |                   5 |
|                 2 |                   2 |  2 | 5      | 5.0000000 | 0.2500000 |                   5 |                   5 |
|                 3 |                   1 |  3 | 6      | 6.0000000 | 0.1000000 |                   6 |                   6 |
|                 3 |                   3 |  3 | 6      | 6.0000000 | 0.1000000 |                   6 |                   6 |
+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+

BUT once I edit it to get the MIN for the "w_real" col and grouping by "lampara_generica_id" all the id cols get messed.

among other things I've tried this:

SELECT lampara_nitide_has_lampara_generica.* , lampara_nitide.* , min(w_real)
FROM lampara_nitide_has_lampara_generica 
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
group by lampara_generica_id 

+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+-------------+
| lampara_nitide_id | lampara_generica_id | id | nombre | w_teorico | w_real    | horas_mantenimiento | coste_mantenimiento | min(w_real) |
+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+-------------+
|                 1 |                   1 |  1 | 4      | 4.0000000 | 0.5000000 |                   4 |                   4 |   0.1000000 |
|                 2 |                   2 |  2 | 5      | 5.0000000 | 0.2500000 |                   5 |                   5 |   0.2500000 |
|                 3 |                   3 |  3 | 6      | 6.0000000 | 0.1000000 |                   6 |                   6 |   0.1000000 |
+-------------------+---------------------+----+--------+-----------+-----------+---------------------+---------------------+-------------+

Anybody has a clue on how this query shall be done?

Interesting link -> here

This question is asked daily on SO. Sometimes it's correctly answered too. See if you can find some similar questions.... I can see someone similar ones just there ------------------------------------->>>>> — Strawberry, Jan 18 '16 at 12:30
I want to get the lampara_nitide_id for a given lampara_generica_id which have the lowest value for w_real in the lampara_nitide table — Adrià ribas, Jan 18 '16 at 12:51

score 0 · Answer 1 · answered Jan 18 '16 at 12:40

Don't use select * in queries where you have an aggregate (i.e. min).

You need to have every column that is not aggregated in your GROUP BY clause, and it will group by those.

This means you need to select only the column(s) you are calling the MIN function on and whatever columns you want the group by to affect.

In other words, if you select the PK field (id) you are always going to get all the rows.

score 0 · Answer 2 · edited Jan 18 '16 at 13:36

This can be done using a temporary table.

SELECT gen.* , nit.* 
FROM 
lampara_nitide_has_lampara_generica gen
     INNER JOIN lampara_nitide nit ON nit.id =    
     gen.lampara_nitide_id,
(SELECT lampara_generica_id, min(w_real) as MIN_W_Real,
    FROM lampara_nitide_has_lampara_generica gen1
    INNER JOIN lampara_nitide nit1 ON nit1.id =   
    gen1.lampara_nitide_id
    GROUP BY gen1.lampara_generica_id ) AS temp
WHERE
gen.lampara_generica_id  = temp.lampara_generica_id AND
nit.w_real = temp.MIN_W_Real

score 0 · Accepted Answer · edited May 23 '17 at 12:15

I believe there are at least 2 solutions:

1) Using INNER JOIN

SELECT lampara_nitide_has_lampara_generica.lampara_generica_id,
lampara_nitide.id as lampara_nitide_id,
lampara_nitide.w_real
FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide ON lampara_nitide_has_lampara_generica.lampara_nitide_id = lampara_nitide.id
INNER JOIN
(SELECT lampara_generica_id, min(w_real) as min_w_real
FROM
(
SELECT *
FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
) lampara_joined
GROUP BY lampara_generica_id) lampara_joined_min
ON lampara_joined_min.lampara_generica_id = lampara_nitide_has_lampara_generica.lampara_generica_id
AND lampara_joined_min.min_w_real = lampara_nitide.w_real
ORDER BY lampara_nitide_has_lampara_generica.lampara_generica_id

SQL Fiddle example

2) Using LEFT OUTER JOIN

SELECT lampara_nitide_has_lampara_generica.lampara_generica_id,
lampara_nitide.id as lampara_nitide_id,
lampara_nitide.w_real  FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide ON lampara_nitide_has_lampara_generica.lampara_nitide_id = lampara_nitide.id
LEFT OUTER JOIN
(SELECT lampara_nitide_has_lampara_generica.lampara_generica_id, w_real
FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
) lampara_joined
ON lampara_joined.lampara_generica_id = lampara_nitide_has_lampara_generica.lampara_generica_id
AND lampara_nitide.w_real > lampara_joined.w_real
WHERE lampara_joined.lampara_generica_id is null
ORDER BY lampara_nitide_has_lampara_generica.lampara_generica_id

SQL Fiddle example

Explanation why this works can be found here: https://stackoverflow.com/a/7745635/5778553

I found another solution:

SELECT lampara_generica_id, id, w_real
FROM
(
SELECT lampara_nitide_has_lampara_generica.* , lampara_nitide.* ,
(Select min(w_real) 
 from lampara_nitide t 
 where t.id in 
 (
   select lampara_nitide_id from lampara_nitide_has_lampara_generica gen 
   where gen.lampara_generica_id = lampara_nitide_has_lampara_generica.lampara_generica_id
 )  
) as MIN
FROM lampara_nitide_has_lampara_generica 
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
) lampara_with_min
WHERE lampara_with_min.MIN = lampara_with_min.w_real
ORDER BY lampara_generica_id

SQL Fiddle example

Another 2 solutions, but only for SQL SERVER:

SELECT lampara_generica_id, lampara_nitide_id, w_real
FROM
(
SELECT lampara_nitide_has_lampara_generica.lampara_generica_id,
lampara_nitide_has_lampara_generica.lampara_nitide_id,
w_real, 
MIN(w_real) OVER (partition by lampara_nitide_has_lampara_generica.lampara_generica_id) as min
FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
) t
WHERE t.w_real = t.min

SQL Fiddle example

SELECT lampara_generica_id, lampara_nitide_id, w_real
FROM
(
SELECT lampara_nitide_has_lampara_generica.lampara_generica_id,
lampara_nitide_has_lampara_generica.lampara_nitide_id,
w_real, 
RANK() OVER (partition by lampara_nitide_has_lampara_generica.lampara_generica_id order by w_real) as rank
FROM lampara_nitide_has_lampara_generica
INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
) t
WHERE t.rank = 1
ORDER BY lampara_generica_id

SQL Fiddle example

Yeep, we already have a working approach but your second approach works too. Could you walk us through it? — Adrià ribas, Jan 18 '16 at 16:13
Yes, there is great idea behind it. It is explained [Here](http://stackoverflow.com/a/7745635/5778553). The most interesting part is with comparing w_real columns. Try to remove the WHERE statement and you will see all results. Results with lowest w_real are joined with nulls. (Because of LEFT OUTER JOIN). Reason why is that, is comparing w_real columns as a join condition. Right part of the join will be not null only if w_real is smaller on the right side. So if there is null on the right side, it means that there is nothing smaller to join. — dusan hrbaty, Jan 18 '16 at 16:29

score 0 · Answer 4 · answered Jan 18 '16 at 16:01

Finally we solved it in two different ways:

The first one

select lampara_generica_id ,  lampara_nitide_id , w_real from 
(
    SELECT lampara_generica_id , lampara_nitide.id lampara_nitide_id , w_real 
    FROM lampara_nitide_has_lampara_generica 
    INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
    order by w_real asc
) t
group by lampara_generica_id
having w_real = MIN(w_real)
order by lampara_generica_id asc, lampara_nitide_id asc;

The subquery returns all the result sorted by w_real asc so when the group by is applied the first result it finds is the one I need. then I use a Having to discard those which w_real is not = the minum fot that group

second one

SELECT lg.*, ln.* FROM lampara_nitide_has_lampara_generica lg
INNER JOIN lampara_nitide on lampara_nitide.id = lg.lampara_nitide_id
INNER JOIN (
    SELECT lampara_generica_id, MIN(w_real) as min_w_real
    FROM lampara_nitide_has_lampara_generica 
    INNER JOIN lampara_nitide on lampara_nitide.id = lampara_nitide_has_lampara_generica.lampara_nitide_id
    group by lampara_generica_id
) ln on lg.lampara_generica_id = ln.lampara_generica_id and lampara_nitide.w_real = min_w_real

In the subquery we return "lampara_generica_id, MIN(w_real)" grouped by lampara_generica_id so we have 1 result for each generic. Then in the otutter query we use the inner join to filter by "lampara_generica_id, MIN(w_real)"

This aproach've got one problem. It will return 2 results if two nitides have the same w_real

SQL join with group by and MIN in sql query

4 Answers4