CODEWITHSUNDEEP
mysqlsql
Ahdriel
Ahdriel

Reputation: 23

Selecting the ID of GROUP BY based on other table value

I have the following tables

produtos

|id(pk), nome|
==============
|27      Oats|
|183     Oats|
|190     Gold|
|25      Gold|

representantes_precos

|id(pk),idproduto(fk),preco|
============================
|1      183           13.00|
|2      190           10.00|
|3      25            15.00|

When i use this query

SELECT   ID, NOME
FROM     ( SELECT * FROM produtos) hey
GROUP BY NOME
HAVING   ( COUNT(NOME) > 0 )

I get the following results:

|id, nome|
==========
|190 Gold|
|27  Oats|

But what i need is this:

|id, nome|
==========
|190 Gold|
|183 Oats|

I want the ID of the Group By be the one who have the highest value on representantes_precos table. I tried with coalesce and sub-selects, joins and others things but had no success.

Any help will be greatly appreciated.

Ps.: Sorry about my poor english.

Upvotes: 1

Views: 140

Answers (2)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT   ID, NOME
FROM     ( SELECT p.ID,p.Nome,r.preco FROM produtos p
          left join representantes_precos r
          on p.id=r.idproduto order by preco desc) test
GROUP BY NOME;

fiddle

Upvotes: 0

PinnyM
PinnyM

Reputation: 35533

Use MAX() to find the largest id (when grouped by nome), then find rows matching that id:

SELECT id, nome
FROM produtos p
WHERE id IN (
  SELECT COALESCE(MAX(rp.idproduto), MAX(p.id))
  FROM produtos p
  LEFT JOIN representantes_precos rp ON p.id = rp.idproduto
  GROUP BY p.nome)

Working SQLFiddle here. Note the LEFT JOIN to allow p records that don't have a corresponding rp, and the use of COALESCE() to first find the MAX in the rp table, and then the p table if not found.

Trying to GROUP on a non-distinct column, and then including other non-grouped columns in your SELECT clause (id in your case), is asking for trouble. Why MySQL permits this and then returns unpredictable results is beyond me.

Upvotes: 1

Related Questions