CODEWITHSUNDEEP
phpmysqlsql
Felipe Vagner
Felipe Vagner

Reputation: 70

Gotta catch all products that have the greatest amount

With the following sample data, I need to get all the products that the amount is 20 because it's the largest quantity. I tried this query however does not seem to work for me because I cannot set the LIMIT to the proper amount since I do not know how many products have that quantity.

SELECT * FROM Product
ORDER BY Quantity DESC
LIMIT 2

+----+---------+----------+
| Id |  Name   | Quantity |
+----+---------+----------+
|  5 | Pencil  |       20 |
|  8 | Stapler |       20 |
|  4 | Pen     |        5 |
|  9 | Sheet   |        3 |
+----+---------+----------+

I need get the products that have most quantity, not necessarily 20, 20 is just an example?

Upvotes: 2

Views: 53

Answers (4)

Alex W
Alex W

Reputation: 38193

Since you edited the question, you would get those with 20 or above, sorted greatest to least with the following query:

SELECT * FROM Product
WHERE Quantity => 20
ORDER BY Quantity DESC;

Upvotes: 1

codelover
codelover

Reputation: 317

if You want to compulsorily use orderby and desc just try

Select * from Product WHERE Quantity = 20 ORDER BY quantity DESC;

formore details on orderby and descBut it is not mandatory in your sitiation

Upvotes: 1

trogdor
trogdor

Reputation: 1656

If you want to select all the products with maximum value of Quantity use:

SELECT *
FROM Product 
WHERE Quantity = (SELECT MAX(Quantity) FROM Product);

Upvotes: 2

Craig
Craig

Reputation: 402

If you want all results where the Quantity is 20, then all you need to do is specify that in the WHERE clause:

SELECT * FROM Product WHERE Quantity = 20;

There's no need to include the ORDER BY if you expect the value to be the same throughout. There's also no need to LIMIT unless you want the first number of results where Quantity is 20.

Upvotes: 2

Related Questions