F.Mysir F.Mysir - 3 months ago 9
SQL Question

How to search max value from group in sql

I am just learning some SQL, so I have a question.

-I have a table with name

TABL


-a variable
:ccname
which has a value "
Bottle
"

The table is as follows:

+----------+---------+-------+--------+
| Name | Price | QTY | CODE |
+----------+---------+-------+--------+
| Rope | 3.6 | 35 | 236 |
| Chain | 2.8 | 15 | 237 |
| Paper | 1.6 | 45 | 124 |
| Bottle | 4.5 | 41 | 478 |
| Bottle | 1.8 | 12 | 123 |
| Computer | 1450.75 | 71 | 784 |
| Spoon | 0.7 | 10 | 412 |
| Bottle | 1.3 | 15 | 781 |
| Rope | 0.9 | 14 | 965 |
+----------+---------+-------+--------+


Now I want to find the
CODE
from the variable
:ccname
with the higher quantity! So I translated like this:

SELECT CODE
FROM TABL
GROUP BY :ccname
WHERE QTY=MAX(QTY)


In a perfect world that would turn as a result
478
.
In the SQL world what should I write in order to get
478
?

Answer

Try this

SELECT CODE
FROM TABLENAme
WHERE QTY = (SELECT MAX(QTY) FROM TablName WHERE Name = :ccname)