CODEWITHSUNDEEP
mysqlsql
Isma Haro
Isma Haro

Reputation: 263

MAX function returning a row

I have 3 tables:

Proveedor

+-----+---------+-----------+
| sid | pnombre | direccion |
+-----+---------+-----------+
|   1 | P1      | La Paz    |
|   2 | Paco    | 31 Pte    |
|   3 | Pepe    | 31 Pte    |
+-----+---------+-----------+

Parte

+-----+-----------+-------+
| pid | pnombre   | color |
+-----+-----------+-------+
|   1 | Palanca 1 | Gris  |
|   2 | Palanca 2 | Gris  |
|   3 | Palanca 3 | Verde |
|   4 | Palanca 4 | azul  |
+-----+-----------+-------+

Catalogo

+-----+-----+-------+
| sid | pid | costo |
+-----+-----+-------+
|   1 |   1 |   100 |
|   1 |   2 |   120 |
|   2 |   3 |    90 |
|   1 |   3 |   150 |
|   1 |   4 |   150 |
+-----+-----+-------+

I'm trying to get the "id of the 'parte'(part) with the highest 'costo' (cost) of the user with name = 'P1' "

My query is this one:

SELECT pid, MAX(costo)
FROM catalogo
WHERE sid IN(SELECT sid
             FROM proveedor
             WHERE pnombre = "P1")
GROUP BY pid;

But my result is this:

+-----+------------+
| pid | MAX(costo) |
+-----+------------+
|   1 |        100 |
|   2 |        120 |
|   3 |        150 |
|   4 |        150 |
+-----+------------+

And I only spect to get 1 row which have to be

+-----+------------+
| pid | MAX(costo) |
+-----+------------+
|   3 |        150 |
+-----+------------+

So the question, What am I doing wrong with my query? how can I return 1 row using MAX() ?

NOTE: I already solved this with another query.
But I'm trying to understand how MAX() works

Query (2) to solve without MAX():

SELECT pid, costo 
FROM catalogo 
WHERE sid IN (SELECT sid 
              FROM proveedor 
              WHERE pnombre = "P1")  
ORDER BY costo DESC 
LIMIT 1;

Upvotes: 0

Views: 42

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The problem with MAXis doesn't bring the row related to that value. Because like this case the MAX value can match several rows.

That is why in the Gordon solution you sort the cost and get the one on the top the get the highest value. But in that case you lost the ties.

SQL Fiddle Demo

In this solution,

  • you first calculate the max value
  • then bring all the records matching that value
  • finally select the one belong to P1

.

  SELECT pid, c.costo
  FROM
  (
    SELECT MAX(costo) costo
    FROM proveedor p
    INNER JOIN Catalogo c
       ON p.sid = c.sid
    WHERE p.pnombre = "P1"
   ) mcosto
   inner join Catalogo c
    on mcosto.costo = c.costo
   inner join proveedor p
    on p.sid = c.sid
   WHERE p.pnombre = "P1";

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

If you are learning SQL, I would advise you to learn explicit join syntax. In addition, you can get a row with the maximum cost by using order by and limit -- aggregation functions are not required:

select c.pid, c.costo
from catalogo c join
     proveedor p
     on c.sid = p.sid
where p.pnombre = 'P1'
order by c.cost desc
limit 1;

Upvotes: 3

Related Questions