1

Let's say I have a OrderDetails table like shown here (click on OrderDetails table on the right) https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

I want to select productID with max number of quantity

I went thru this answer Is it possible to use Aggregate function in a Select statment without using Group By clause?,

so this answer makes sense if I am summing/averaging something then putting the other column(without aggregation) in a group by clause

for example 

select depName, avg(salary)
from department

here you're averaging without group by so the query is ambiguous that avg of what depName ?

but the following query is what I am talking about if I have a situation like this (OrderDetails table and select ID of max quantity) can I use the below query ? 

select ProductID, max(Quantity)
from 
OrderDetails

the query shown above gives me desired result, it gives me the ID which has the max quantity. I know there are other ways of performing the same query like put them and desc. order select top 1st row's id etc..

any help will be appreciated Thanks !

Anuj
  • 113
  • 11
  • you can actually try running on the 1st link from the question, havent tried on mysql workbench tho – Anuj Aug 30 '19 at 20:29
  • 1
    `select ProductId, max(Quantity) from OrderDetails` does *not* return the ProductId associated with the row that has the max Quantity value unless by accident. It returns the ProductId of the first row. – Booboo Aug 30 '19 at 20:33
  • I'm not sure what you are actually asking. You seem to know that you can use `LIMIT 1` (or `TOP 1` for sql-server). See: [which-row-is-selected-in-group-by](https://stackoverflow.com/questions/57637335/which-row-is-selected-in-group-by) – Paul Spiegel Aug 30 '19 at 20:34
  • You need `select ProductId, Quantity from OrderDetails where Quantity = (select max(Quantity) from OrderDetails));` This can return multiple rows unless you put a limit on it. – Booboo Aug 30 '19 at 20:35
  • @RonaldAaronson that's what I was thinking , I mean if I do sum() or avg() 1st row's id is returned, but not in this case – Anuj Aug 30 '19 at 20:37
  • MySQL and SqlLite are too loose when it comes to syntax rules and restrictions, `select ProductId, max(Quantity) from OrderDetails` is not the correct syntax. – alibttb Aug 30 '19 at 20:39
  • 1
    @Anuj I suppose it depends on what is meant by "first row", i.e. whether ProductId is your primary key or not. I just tested this on a similar table where I did a `select a, max(b) from t` where a is a primary key and if I do a `select * from t` without specifying s sort order the first value of `a` that is returned is what I call the `a of the first row`. In any case, what you will get is a crap shoot. – Booboo Aug 30 '19 at 20:50

1 Answers1

0

It's how SQL works: max, min, avg ... etc are called aggregate functions and they require group by clause to work when the query have them beside another column in the projection.

Your query SELECT c1, max(c2) from T1; is still ambiguous as you might have more than c1 values which have the max(c2) in the table, it could have been implemented to return more than one row, but this is NOT how SQL was designed.

When you use aggregate functions you specify GROUP BY to select your ranges where a single value of the aggregate function is returned for each range.

The way to get the result you want is like this:

SELECT ProductId, Quantity 
FROM OrderDetails 
WHERE Quantity = (SELECT MAX(Quantity) FROM OrderDetails);
alibttb
  • 526
  • 4
  • 19
  • okay i get it why it's ambiguous but in your case too where Quantity = there can be more than 2 quantities which have same highest value but different productIDs then ? – Anuj Aug 30 '19 at 20:42
  • yes and here you are selecting all of them, look at `SELECT ProductId, Quantity ` this projection has no aggregates so it's free to return all the rows matching the criteria. – alibttb Aug 30 '19 at 20:44
  • Thanks makes sense :) appreciate for your help – Anuj Aug 30 '19 at 20:49